Copy Data from one SQL Table and Insert it into an other Table

The best way to usually handle this is bulk copy in to a temporary table the data then you can do your normal query like you would.

I don't know your models so I leave it as a experiment to you on how to do that with a List, but if you could open two connections at once you can easily do it like this using SqlBulkCopy to transfer between the two servers.

using (var sourceConnection = new SqlConnection(sourceConnectionString)) using (var destinationConnection = new SqlConnection(destinationConnectionString)) { sourceConnection.Open(); destinationConnection.Open(); var createTableQuery = "create table #t (id uniqueidentifier, name nvarchar(100), adresse(nvarchar(100))"; using (var createTableCommand = new SqlCommand(createTableQuery, destinationConnection)) { createTableCommand.ExecuteNonQuery(); } using (var selectCommand = new SqlCommand("SELECT id, name, adresse FROM table1")) using (var selectReader = selectCommand.ExecuteReader()) using (var destBulkInsert = new SqlBulkCopy(destinationConnection)) { destBulkInsert.DestinationTableName = "#t"; destBulkInsert.WriteToServer(selectReader); } var mergeQuery = "INSERT INTO table2(id, name, adresse) SELECT * FROM #t WHERE #t.id NOT IN(SELECT id FROM table2)"; using (var mergeCommand = new SqlCommand(mergeQuery, destinationConnection)) { mergeCommand.ExecuteNonQuery(); } } 

1. On server1 create linked server and

   EXEC master.dbo.sp_addlinkedserver @server = N'server2', @srvproduct=N'SQL Server'

   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'server2',@useself=N'False',@locallogin=NULL,@rmtuser=N'USER_NAME',@rmtpassword='########'

2. Execute your query from server1 as

   use dbName1
   go

   INSERT INTO server2.dbName2.dbo.table2(id, name, adresse)
   SELECT * FROM dbo.table1 t1
   left join server2.dbName2.dbo.table2 t2 on t1.id=t2.id
   where t2.id is null