Having A Lot Of Sql Query Conditions

I am updating remote MySQL database by comparing row by row from local MSSQL one. Idea was to do update in 3 steps: 1: select all ID's from local and execute following query on rem

Solution 1:

So I have this done this before. A long time ago though so I might miss a step but I hope you will get the idea.

1. Setup a linked server on the MSSQL database to the MySQL database. See this Linked MySQL Server article

2. SELECT all the table you want from the linked server into a temp table on the MSSQL SQL. Something like SELECT * INTO #temp FROM linkedserver.tablename however it would be better to create a proper temp table and index the columns you will be joining on i.e.

   CREATETABLE #Test
   (
   ID INTPRIMARY KEY NOTNULL
   )
   INSERTINTO #Test
   SELECT*FROM linkedserver.tablename
   

3. Do a LEFT/RIGHT JOIN to find new ID on the local machine and insert them into the remote server via linked server. See this link for more information on how to use LEFT/RIGHT joins to get the new records when comparing two tables Using Left join to find new rows

4. Update the remote server with a UPDATE statement and JOIN in it. So basically using a INNER JOIN do a update to the remote server with the values in the temp table.

Now there might be some errors you run into with the syntax post them here and I can try and resolve them for you. However I have used this technique to synchronize between MySQL and MSSQL servers and it works pretty well. As it is SETS based and not RBAR based it is very fast as well.