Migrate data between two SQL databases with identity column

Question

Here is the scenario... I've two databases (A & B) with same schema but different records. I'd like to transfer B's data into corresponding tables in DB A.

Lets say we have tables named Question and Answer in both databases. DB A contains 10 records in Question table and 30 in Answer table. Both tables have identity column Id starting with 1(& auto increment), and there is 1 to many relation between Question and Answer.

In DB B, we have 5 entries in Question table and 20 in Answer.

My requirement is to copy data of both tables from source DB B into destination DB A without having any conflict in identity column while maintaining the relation between two tables during data transfer.

Any solution or potential workaround would be highly appreciated.

You really should provide some details about your table. Here is a great place to start. — Sean Lange
– Sean Lange, Commented Sep 18, 2018 at 13:13

Raj · Accepted Answer · 2018-09-19 04:28:30Z

2

I will not write SQL here but here is what I think can be done. Make sure to use Identity insert ON and OFF.

Take maxids of both tables from DB A like A_maxidquestion and A_maxidanswer.
Select from B_question . In select column add derived col QuestionID+A_maxidquestion.This will be your new ID.
Select from B_Answer . In select column add derived col AnswerID+A_maxidanswer and fk id as QuestionID+A_maxidquestion.

Note- Make sure Destination table is not beeing used by any other process for inserting values while you are inserting

edited Sep 19, 2018 at 4:28

answered Sep 18, 2018 at 9:36

Raj

5125 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sean Lange Over a year ago

This is pretty brittle. What happens if there are other people in the system at the time these inserts are running?

Raj Over a year ago

SeanLange - yes that can be possible but it was mentioned its a migration though I have added note.

Sean Lange · Accepted Answer · 2018-09-18 13:12:53Z

0

One of the best approaches to something like this is to use the OUTPUT clause. https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017 You can insert the new parent and capture the newly inserted identity value which you can use to insert the children.

You can do this set based if you also include a temp table which would hold the original identity value and the new identity value.

With no details of the tables that is the best I can do.

answered Sep 18, 2018 at 13:12

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Migrate data between two SQL databases with identity column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related