April 18, 2007 at 3:51 pm
I have a table “A” that has an identity column that is the primary key and a table “B” that has a foreign key to table “A”. I want to duplicate some of the rows in table “A” and then make a few changes to the duplicated data in table “A”. I also want to duplicate the corresponding rows in table “B” that were copied in table “A”. The problem is how do I get the FK in the duplicated data in table “B” to point to the new duplicated data in table “A”?
April 18, 2007 at 7:19 pm
Hopefully someone can come up with a better way to do this.
First, create two temporary table that have the same fields as the original two tables. Copy all of your records (A table records and all related B table records) to their respective temp table. Make your changes to those records you need to change.
Now, loop through table #A
declare @id int
declare @newid int
select top 1 @id = id from #A
while @id is not null
begin
insert into A select * from #A where ID = @id
select @NewID = scope_identity()
insert into B select * from #B where AID = @id
delete #B where AID = @ID
delete #A where ID = @ID
set @id = null
select top 1 @id = id from #A
end
Russel Loski, MCSE Business Intelligence, Data Platform
April 18, 2007 at 10:34 pm
Thank you! Much better than the solution I came up with!
April 19, 2007 at 5:34 am
One major correction!
Replace the following line with something like the second line. All of the non-AID columns and exclude the identity col if you have one (which means you will need to specify columns in the insert part)
insert into B select * from #B where AID = @id
insert into B select @NewID, col2, col3 from #B where AID = @id
Russel Loski, MCSE Business Intelligence, Data Platform
April 19, 2007 at 3:05 pm
Thank you for all of your time!
April 19, 2007 at 10:59 pm
Just thought you would like to know it works great. The only other change was the insert into A also had to be a list of columns.
Otherwise it worked as coded!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply