SQL Server Migration

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