CODEWITHSUNDEEP
sqlssisdata-migration
Hungry Beast
Hungry Beast

Reputation: 3725

SSIS Data Migration Primary Key Identity Conflicts

We have developed a large data migration from one DB schema to the other. We had built it based on the idea that the destination DB would be empty, however months ago we started putting clients on the new application which means their data is being housed in the new schema (the destination DB).

Now we're in a situation where the primary keys could overlap from the source to the destination DB and we're struggling to come up with a solution. The only solution I can think of is to check if the ID exists in the destination, updated the ID in the source to be 1 more than the greatest ID in the destination, and then migrate the record. This seems really cumbersome to have to do for hundreds of tables. Any ideas?

Upvotes: 1

Views: 1141

Answers (1)

user514848
user514848

Reputation: 46

Sorry I don't know anything about SSIS but the following are a few ways to solve the problem using SQL.

When inserting into the destination tables, do not insert identities. As rows are inserted, capture the newly inserted identities and the old identities in a mapping table, see MERGE + OUTPUT INTO. Use the mapping table to update the tables that haven't been inserted, substituting the old identities with the new identities.

Of course for this to work, insertion into tables has to be done in an order that won't cause foreign key or constraint violations.

If you're not into doing all that, and you can lock users out of tables for short periods of time, DBCC CHECK INDENT could be used to 'reserve' identities. These new identities can then be used to update the old data and then insert with SET IDENTITY_INSERT ON.

Upvotes: 2

Related Questions