use YourNewDBName
go
exec sp_addlinkedserver @server = N'YourLinkedServerName', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'YourSqlServerIpAddress'

exec sp_addlinkedsrvlogin @rmtsrvname=N'YourLinkedServerName',@useself=N'False',@locallogin=NULL,@rmtuser=N'YourSAName',@rmtpassword='YourPassword

begin
declare @tablename varchar(50)
declare csr cursor
for(
select o.name
from [YourLinkedServerName].[YourOldDBName].sys.indexes as i 
join [YourLinkedServerName].[YourOldDBName].sys.objects as o on i.object_id = o.object_id
join [YourLinkedServerName].[YourOldDBName].sys.dm_db_partition_stats as ddps on i.object_id = ddps.object_id and i.index_id = ddps.index_id 
where i.index_id < 2 and o.is_ms_shipped = 0 and ddps.row_count > 0
)
--disable foreign key
SET FOREIGN_KEY_CHECKS=0
open csr
fetch next from csr into @tablename
while @@FETCH_STATUS = 0
	begin
		declare @sqlstr varchar(200)
		set @sqlstr = 'insert into ' + @tablename + ' select * from [YourLinkedServerName].[YourOldDBName].dbo.' + @tablename
		begin try
			exec (@sqlstr)
		end try
		-- WARNING catch without interrupt
		begin catch
			--composite primary key
			print @sqlstr
		end catch
		fetch next from csr into @tablename
	end
close csr
deallocate csr
--enable foreign key
SET FOREIGN_KEY_CHECKS=1
end
GO

set IDENTITY_INSERT dbo.CompositePrimaryKeyTable ON

insert into dbo.CompositePrimaryKeyTable(PrimaryKey1,PrimaryKey2,OtherField,...)
select PrimaryKey1,PrimaryKey2,OtherField,... from [YourLinkedServerName].[YourOldDBName]. dbo.CompositePrimaryKeyTable

set IDENTITY_INSERT dbo.CompositePrimaryKeyTable OFF