×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Custom Sync tool

Custom Sync tool

Custom Sync tool

(OP)
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

RE: Custom Sync tool

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

CODE

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

For Each drSQL As DataRow in dtSQLServer.Rows
SqlStr &= "'" & drSQL.Item("ID") & "',"
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:

CODE

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
'Filter the SQL Server results, by each ID found in MySQL
dtSQLServer.DefaultView.RowFilter = "ID=" & drMySQL.Item("ID")
cmd.Parameters("@Fname").Value = dtSQLServer.DefaultView(0).Item("Fname")
cmd.Parameters("@Lname").Value = dtSQLServer.DefaultView(0).Item("Lname")
cmd.Parameters("@ID").Value = dtSQLServer.DefaultView(0).Item("ID")
cmd.ExecuteNonQuery()
Next

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

CODE

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

For Each drMySQL As DataRow in dtMySQL.Rows
SqlStr &= "'" & drSQL.Item("ID") & "',"
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:

CODE -->

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
cmd.Parameters("@Fname").Value = drSQL.Item("Fname")
cmd.Parameters("@Lname").Value = drSQL.Item("Lname")
cmd.Parameters("@ID").Value = drSQL).Item("ID")
cmd.ExecuteNonQuery()
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!

RE: Custom Sync tool

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.

RE: Custom Sync tool

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close