Solved

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

Posted on 2004-09-08
4
211 Views
Last Modified: 2012-06-27
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)
 
0
Comment
Question by:vinny45
  • 3
4 Comments
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12009162
Are there foreign keys and/or primary keys defined on the tables? Do they autonumber (IDENTITY)?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12009165
In other words, could you script out the tables with Enterprise Manager and post them here?
0
 

Author Comment

by:vinny45
ID: 12009322
yes they do autonumber, iam not sure what the table looks like yet, but its pretty much standard stuff with primary and foriegn keys
0
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 500 total points
ID: 12009467
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

...etc.

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

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now