Picking up IDs on auto data transfer or back porting them to source

[jrbarnett]

I have an application that feeds three sets of data, lets call them A, B and C from server X to server Y.

When item A gets posted into server Y, it gets issued ID numbers. Before items B and C can be posted into the import tables on server Y, the ID number issued to item A needs to find its way back to X so that they can be included in the data export as a key field (or at least reliably matched up on the way across).
Does anybody have any ideas on how to do this?

Server X is running SQL 2008, server Y is running SQL 2000 (although the app does support 2005, so I could possibly upgrade it).

John

 

[danvlas]

Can you add the ID for item A from server X in the set that you transfer from X to Y?

If yes, you will have the pair: Y_ID and X_ID on server Y.
When you transfer set B, include the X_ID from server X, join on X_ID from server Y, grab the corresponding Y_ID and populate the foreign table.

The same for set C.

Hope I understood the question





Daniel Vlas
Systems Consultant