December 4, 2010 at 7:01 am
Hi
I'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----<C
Assuming I am duplicating one row from table A in this operation then:
A=1 row
B=1000 rows connected to A
c=50 rows connected to B
Updating 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.
December 4, 2010 at 4:18 pm
Hi,
The chain of related tables you mention means the following:
A
PK keyA
B
PK keyB
FK keyA
C
PK keyC
FK keyB
there PK means primary key and FK means a foreign key.
What is really the problem? Of course you have to loop when you insert the connected in B resp C as they are (or should be) unique. Of course, if you already have a collection already generated in your code, you can insert them in one statement. In Sql 2005 it is somewhat cumbersome, Sql 2008 allows multiple row insert.
You don't have to loop when you are selecting though.
Please describe the problem more in detail, if I have misunderstood.
Cheers
December 6, 2010 at 3:02 pm
If you duplicate rows in B then the ID's change so C has to reflect this. Maybe you know something I don't but I've had to resort to creating an additional column in B to store the original id 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.
/*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_id
December 6, 2010 at 3:43 pm
Hi,
I am sorry I misunderstood your problem. So what is the reason to duplicating rows? Is it some sort of version system?
Regards
Istvan
December 6, 2010 at 5:03 pm
Two purposes. First I need a copy button on the website that duplicates the selected projects that a customer has created using our service.
Secondly I want to be able to transfer an entire account to another database, again the database is not empty so the ID's will all be different once inserted.
The only other way I can think of is using cursors (something I have no idea how to use anyway) or perhaps to use a temp table to avoid having to insert the additional column.
One table has only 4 columns but has millions or rows and is about 20GB in size so adding an extra column for this (which I'm guessing would probably need an index for speed) would not be a good idea.
I searched a lot to find a solution to this problem and I'm surprised that this doesn't come up more often?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply