Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmeagor
Starting Member
16 Posts |
 Posted - 2010-12-04 : 09:04:07
|
| HiI've been searching for a solution to this problem for some time and cannot find a solution.How do you duplicate rows of a table and keep referential integrity with the foreign keys in the related/connected tables?Example. I have three tables which have a many to one relationship, like so... A---<B----<CAssuming I am duplicating one row from table A in this operation then:A=1 rowB=1000 rows connected to Ac=50 rows connected to BUpdating the foreign key in B is easy since its just one new ID to insert but updating the foreign key in C is impossible as it could be any one of 1000 new identity values.Currently the only way I can think to do this is to script some horrendously slow ASP script to loop through each row in B and update C accordingly but there was to be a better way than that. What if someone wanted to copy 10 rows from table A, it would be a total mess!Please can someone help me with this. I'm assuming that duplicating is a pretty common requirement so is there a standard way of doing this? Was I supposed to link up the tables in the database designer or something to avoid this kind of problem?NB. I'm an extreme noob with SQL.David Meagor |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-04 : 12:33:49
|
| Please post the DDL for the 3 tables. |
 |
|
|
dmeagor
Starting Member
16 Posts |
Posted - 2010-12-06 : 17:06:14
|
| I've found a somewhat poor solution to the problem. I've had to create an additional column in the parent table (in the example below testB) to store the original id from which the row was duplicated and then do an inner join on it to get the new ID. Seems messy and not ideal to me.For anyone else struggling with this the solution below works. The two example tables contain just an id column and a text field (plus the additional copiedFrom_id column to make the inner join work./*duplicate rows in testA table and keep a copy of the original id for use later */insert into testA (mytext,copiedFrom_id) select mytext,id from testA/*duplicate rows in testB and join on the original id in order to get the new one*/insert into testB (moretext,a_id) select testB.moretext,testA.id from testB inner join testA on testA.copiedFrom_id=testB.a_idDavid Meagor |
|
|
|
|
|
|
Duplicating rows in multiple connected tables