sql server - 1 sql with 2 different sqlconnections using c# -

i have 2 different connections located on different sql servers, source server , target server. want compare table exists in both servers , align target server table source server one.

sqlconnection src = (databaseconnection.getsqlconnection());  sqlconnection trg = new sqlconnection(properties.settings.default.idcconnectiondev);  src.open(); trg.open();  sqlcommand source = new sqlcommand("select * source table", src); sqlcommand traget = new sqlcommand("select * target table", trg);  sqldatareader drsrc = source.executereader(); sqldatareader drtrg = traget.executereader();  datatable tbl1 = new datatable(); tbl1.load(drsrc);  datatable tbl2 = new datatable(); tbl2.load(drtrg); 

now question how run following query:

(select * source table) except (select * target table) 

or other sql query using tables both connections.

you not able run sql statement across 2 different data tables, since on different connections.

1. you create linked server on 1 server or other, , run query on 1 server joined other server sql.
2. you perform join client-side using other sql.

to perform join client-side, consider using merge join minimize client-side memory needs if these tables large. adding order by clause both select statements sort both of them primary key, or unique key if don't have primary key. loop through 1 side , compare current record of other using <, >, , == comparisons check if records in 1 or other. determine if record in source , not target, if there record in target not source, or if record existed in both. consider using idatareader instead of datatable in case prevent having load both tables in memory if tables large.

alternatively, use hash join (which how linq solve it) if tables not enormous , have plenty of ram client side. loading primary/unique key of 1 side dictionary , testing every record other side see if in dictionary. easier code, , may faster, requires more memory usage. use idatareader instead of datatable save client-side memory, have have @ least 1 side of join in memory (in dictionary)

finally, use linq queries perform client-side joins in sql-like manner. smallest amount of code. have leave in datatable, , linq use dictionary or hashset under covers, have 3 copies of tables in memory work.