Copying data without overwriting

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Copying data without overwriting

Postby lreichenbach » Wed Feb 17, 2010 7:42 pm

Howdy gang!

I have what I am hoping is a simple question...

I have to update an older version of our database schema to our new structure (that part was easy), then I have to copy the old data to the new tables. For some of the data I was able to generate my own mappings so I did not overwrite current data or lose legacy data.

So far so good. :)

I do have a couple of tables that have me stymied. To simplify things the table looks like this:

ID (identity) | CallID (our software generates this and needs to be unique)
-------------- --------
1 1234
2 1235
3 1236
etc...

The problem is I need to ensure that although there will be DUPLICATE CallID's across the tables - I need NEW CallID's for the old.

Can this be done within the SDK?
Should I just build the SQL by hand and do it that way?

Thanks for the help!
Louis
lreichenbach
 
Posts: 2
Joined: Wed Feb 17, 2010 7:09 pm

Postby lreichenbach » Thu Feb 18, 2010 6:02 pm

I found a solution:

- I grabbed the max(callid) number from the new DB
- Updated all the callID's on the archived data to callid + max so they would start counting up from the largest callid
- Now with the archived rows completely different from the current rows I was able to use RedGate's SDK to merge the data together.

Certainly easier than a pure SQL solution but not as nice if the SDK had a clean way to do this and preserve data. Oh well...close enough!

Louis
Louis
lreichenbach
 
Posts: 2
Joined: Wed Feb 17, 2010 7:09 pm

Postby Brian Donahue » Fri Feb 19, 2010 12:48 pm

Hi Louis,

Thanks for the update and I'm glad to see that you found a solution. SQL Comparison SDK is all obout normalizing data, so it really doesn't give you the ability to "transform" the data when it's being synchronized (to use a DTS term!).

The solution would probably lie in some custom SQL -- if you didn't already have an IDENTITY on the ID column, I would have suggested putting an identity on CallID and seeding it to the max value already in the table. I don't know of a way of doing it in the SDK, though.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests