1 sql with 2 different sqlconnections using c #
I have 2 different connections located on different sql servers, Source Server and Target Server. I want to compare a table that exists on both servers and align the Target Server table with the original server.
SqlConnection src = (DataBaseConnection.GetSqlConnection());
SqlConnection trg = new SqlConnection(Properties.Settings.Default.IDCConnectionDEV);
src.Open();
trg.Open();
SqlCommand source = new SqlCommand("SELECT * FROM Source table", src);
SqlCommand traget = new SqlCommand("SELECT * FROM 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 I am asking the question how to execute the following query:
(select * from Source table) except (select * from Target table)
or any other sql query using tables from both connections.
source to share
You will not be able to run the SQL statement on two different data tables as they are on different joins.
- You can create a linked server on one server or another and then run a query on one server that is joined to another server with SQL.
- You can execute the client side of the connection using something other than SQL.
To perform a client-side join, consider using a merge join to minimize client-side memory requirements if these tables are large. This will add a suggestion ORDER BY
for operators SELECT
to sort both of them using a primary key or some unique key if you don't have a primary key. Then you pass through one side and compare with the current record another using comparisons <
, >
and ==
to check whether the records were in one or the other. You can then determine if the record was in the source and not the target, if the target was the record and not the source, or if the record existed in both. In this case, consider using IDataReader
insteadDataTable
so that there is no need to load both tables into memory if the tables are really large.
Alternatively you can use a hash join (which linq will most likely help) if the tables are not huge and you have a lot of client-side RAM. This would be loading the primary / unique key from one side to Dictionary
, and then testing each record for the other side to see if it is in Dictionary
. Easier to code, and can be faster, but requires more memory usage. You can use IDataReader
instead DataTable
to save client memory, but you will need to have at least one side of the connection completely in memory (in Dictionary
)
Finally, you can use Linq queries to make client-side connections in a SQL-like manner. This will be the smallest amount of code. You will have to leave it in DataTable
, and Linq will most likely use Dictionary
or Hashset
under the covers, so you have 3 copies of the tables in memory for that.
source to share
You can use Linked Servers ( https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine ) in yours SqlCommand
and then take a look for this example to use LEFT
or RIGHT JOIN
to get the difference between the two tables see for example: http://www.dofactory.com/sql/right-outer-join
source to share