Methodology for synchronising two lists?

[Cylindric]

Hi there folks,

I have a bit of a problem that I'm not sure how best to tackle. I think I might be missing a trick somewhere.

I am working on an in-house tool that is going to synchronise one database (MSSQL) with another completely different database (MySQL), containing completely different schema.

What I would like some advice on is the best way of doing the processing. My problem is mainly (I think ) that I'm not very familiar with datasets and the other great data-handling structures in .net, so may well be missing something obvious.

This is what I'm doing at the moment:

SELECT data from source (reference) table
For each record
  Populate an object with the field-values
  Add the object to a SOURCE collection
 
SELECT data from target table
For each record
  Populate an object with the field-values
  Add the object to a TARGET collection
 
(I now have two collections)
 
For each Object in SOURCE
  If Exists In TARGET Then compare with TARGET and update target table if necessary
  Else Add new record to target table
 
For each Object in TARGET
  If not updated yet, then it's been removed from SOURCE so delete from target table

Basically, I'm caching both sets of data, updating/adding records, then deleting anything that wasn't touched.

Apologies for a slightly rambling post, I'm not entirely sure how to articulate the problem, or how much actual code to paste.

Thanks in advance,

Mark

 

[cjard]

I'd do it in sql, forget the client completely.. I dont know how to write a query in SQL Server that selects from another database. in oracle it's

SELECT * FROM table@server

where server is a defined database link name.

Hence we have SQLs that look like:

INSERT INTO
  destTable(colA,colb,colC)
SELECT
  colX,colY,colZ
FROM
  source_table[U][COLOR=#22229c]@foreign_database[/COLOR][/U]
  LEFT OUTER JOIN
  [EMAIL="dest_table@foreign_database"]dest_table[/EMAIL]
  ON
    criteria
WHERE
  dest_table.primary_key IS NULL

This basically performs a join between two tables in different systems, and only the records that dont exist in destination (as indicated by left join producing a null) are inserted..

Hope that makes sense

 

[cjard]

oh.. I jsut failed to notice that the two RDBMS are different..

See if you can do it through ODBC.. i.e. see if the SQLServer can read/write an odbc link to MySQL

 

[Cylindric]

Heh, that's always my first inclination. I come from a database background before a programming background, unfortunately there's a small mountain of comparison and checking code in between the "input" and "output" stages. Otherwise I'd probably have tried a more "nuts'n'bolts" level solution using DTS or something.

There is a common key between the two sets of data, so I'll probably stick to something like what I'm doing at the moment, although I'll have to optimise the code somewhat, it's terribly slow at the moment. (Doesn't help that one half of the DB is on a webserver).

Hmm, perhaps sorting the two collections by the "primary key", and processing both in tandem would help. Then I can add/delete/insert as I go.

For Each SOURCE Item
  If SOURCE.KEY < DEST.KEY Then
    Add New Target Record

  ElseIf SOURCE.KEY = DEST.KEY Then
    Compare-and-update Target Record Where Target.Key = DEST.KEY
    Next DEST

  Else
    Delete Target Record Where Target.Key = DEST.KEY
    Next DEST

  End If
Next
If Not At End Of DEST
  Delete Target Record Where Target.Key In Remaining DEST.KEYS
End If

Wah, that's some nasty pseudo-code, but I think talking about it might have helped This would be so much easier if Iwasn't the only techie in the building

 

[cjard]

I was thinking of something like this:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=340912

Forget VB if possible..

Third hit in http://www.google.co.uk/search?sour...5,GGLG:en&q=sqlserver+select+from+mysql+table

 

[Cylindric]

Ahh, thanks. I'm just waiting for the registration email to arrive, and I'll read it

 

[cjard]

Ahh, thanks. I'm just waiting for the registration email to arrive, and I'll read it

the google snippet was:

How to Create Linked Server for a MySQL database using SQL Server ...[SIZE=-1]Create a Linked Server in SSMS for the MySQL database SSMS (SQL Server Management Studio -> Expand Server Objects -Right Click Linked Servers -> Select New ...
www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=3409[/SIZE]

which got my interest.. Hope you get soemthing up soon; let us know!