• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Best way to move parent child data from one db to another

What is the best way to move parent child(multiple) data to from one db to another using code C# or t-SQL in one transaction (if this is a good idea)
  • 3
1 Solution
Are there foreign keys and/or primary keys defined on the tables? Do they autonumber (IDENTITY)?
In other words, could you script out the tables with Enterprise Manager and post them here?
vinny45Author Commented:
yes they do autonumber, iam not sure what the table looks like yet, but its pretty much standard stuff with primary and foriegn keys
in that case, in the destination database:

begin tran

delete from table1

set identity_insert table1 on

insert into table1(...list of columns...) select ...list of columns... from sourcedatabase..table1

set identity_insert table1 off


commit tran

Make sure you go from bottom to top in order, e.g. if table1 references table2 which references table3, go

delete table1, delete table2, delete table3
insert table3, insert table2, insert table1

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now