Custom Sync tool

[bestmakis]

Hi

How I can create one application that get results from one MS SQL Server query
compare them with one MYSQL Server query and if it is equal then do update else do insert.

For example, let say I have my source sql query
Select ID, FName, LName From TBNames
Results:
ID | FName | Lname
1 | John | Eaglin
2 | Harry | Baggerly
3 | Charlie | Abbett
4 | George | Dent

Then from Mysql query I have the result
Select ID, fName, LName from MYNames where ID = {MSSQL_ID};
ID | FName | Lname
1 | John | Smith
2 | Tomas | Ford

I need to pass through ID field and update ID 1 and 2 from Smith to Eaglin
from Tomas Ford to Harry Baggerly and insert the other 2 records.
Running the same select on MYSQL I get the result
ID | FName | Lname
1 | John | Eaglin <-- Last Name changed
2 | Harry | Baggerly <-- Both Names changed
3 | Charlie | Abbett <-- Inserted
4 | George | Dent <-- Inserted
The problem is that queries are many with lot of different fields and tables.
Is there any way I can pass fields names and results as variables to the update or insert query?

Thank you

 

[jebenson]

Step 1: Select all records from SQL Server. Loop through those records and build a SQL Where clause with the ID numbers, like so:

Dim SqlStr As String = "Select * from <MySQLTableName> where ID IN("

For Each drSQL As DataRow in dtSQLServer.Rows
[indent]SqlStr &= "'" & drSQL.Item("ID") & "',"[/indent]
Next

'remove trailing comma and close parentheses
SqlStr = SqlStr.Substring(0, SqlStr.Length - 1)
SqlStr &= ")"

Run this SQL query to get all records with matching IDs from the MySQL table.

Step 2: Loop through the MySQL results and update with SQL Server data:

Dim MySQLStr As String = "Update <MySQLTableName> Set Fname=@Fname, Lname=@Lname Where ID=@ID"
Dim cmd As OleDbCommand
cmd = New OleDbCommand(MySQLStr, <MySQLConnection>) 'note: I'm not familiar with how VB connects to MySQL.  I assume you already know how to connect and update the DB.
cmd.Parameters.Add("@Fname")
cmd.Parameters.Add("@Lname")
cmd.Parameters.Add("@ID")

For Each drMySQL As DataRow in dtMySQL.Rows
[indent]'Filter the SQL Server results, by each ID found in MySQL[/indent]
[indent]dtSQLServer.DefaultView.RowFilter = "ID=" & drMySQL.Item("ID")[/indent]
[indent]cmd.Parameters("@Fname").Value = dtSQLServer.DefaultView(0).Item("Fname")[/indent]
[indent]cmd.Parameters("@Lname").Value = dtSQLServer.DefaultView(0).Item("Lname")[/indent]
[indent]cmd.Parameters("@ID").Value = dtSQLServer.DefaultView(0).Item("ID")[/indent]

[indent]cmd.ExecuteNonQuery()[/indent]

Next

Step 3: Use the MySQL Results from Step 2 to get IDs in SQL Server that are not in MySQL:

Dim SqlStr As String = "Select * from <SQLServerTableName> where ID NOT IN("

For Each drMySQL As DataRow in dtMySQL.Rows
[indent]SqlStr &= "'" & drSQL.Item("ID") & "',"[/indent]
Next

'remove trailing comma and close parentheses
SqlStr = SqlStr.Substring(0, SqlStr.Length - 1)
SqlStr &= ")"

Step 4: Loop through the results from Step 3, and Insert each record into MySQL:

Dim MySQLStr As String = "Insert Into <MySQLTableName> FIELDS (Fname, Lname, ID) VALUES (@Fname, @Lname, @ID)"
Dim cmd As OleDbCommand
cmd = New OleDbCommand(MySQLStr, <MySQLConnection>) 'note: I'm not familiar with how VB connects to MySQL.  I assume you already know how to connect and update the DB.
cmd.Parameters.Add("@Fname")
cmd.Parameters.Add("@Lname")
cmd.Parameters.Add("@ID")

For Each drSQL As DataRow in dSQLServer.Rows
[indent]cmd.Parameters("@Fname").Value = drSQL.Item("Fname")[/indent]
[indent]cmd.Parameters("@Lname").Value = drSQL.Item("Lname")[/indent]
[indent]cmd.Parameters("@ID").Value = drSQL).Item("ID")[/indent]

[indent]cmd.ExecuteNonQuery()[/indent]

Next

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Andrzejek]

If you would have all the data available in MySQL (including the data from SQL Server), all of that would a lot easier.

According to this article "You have to push the data from the ms sql server side into MySQL [temporary?] tables"


---- Andy

There is a great need for a sarcasm font.

 

[bestmakis]

Thank you for your help.

I will check both options and I be back to you with best option for me.

Again thank you all for your help