Hope some one can help me, I would like to copy rows in same table and that table has relation to another table that I have to copy related row accordingly:
Table1
table1Id table0Id otherColumn 1 3 8 2 3 9 3 4 6
I copied rows with table0Id = 3
Table1
table1Id table0Id otherColumn 1 3 8 2 3 9 3 4 6 ------------------------- 4 3 8 5 3 9
I would like to do the same to Table2 depending to the Table1 Ids like this:
Table2
table2Id table1Id otherColomn 1 1 0 2 2 5 3 3 8
Table2
table2Id table1Id otherColomn 1 1 0 2 2 5 3 3 8 ----------------------- 4 4 new Id 0 5 5 new Id 5
As you see row 1 and 2 are copied in table2 but they have new Ids from newly added rows in table1. I know how to do the firs part but I’m stuck at the second part.
Advertisement
Answer
Thanks for your answers, I figured it out like this by using output and identity:
Declare @temp table(tempId int,tempName nchar(10),tempOther bit, rownumber int identity) insert into @temp select Id,Nam,other from dbo.Table_1 where Nam = 'ToCopy' Declare @tempIds table(outputId int,rownumber int identity) Declare @finalTemp table(OldId int,new_id int) select * from dbo.Table_1 select * from dbo.Table_2 insert into dbo.Table_1 output inserted.Id into @tempIds select tempName,tempOther from @temp insert into @finalTemp select oldT.tempId, newT.outputId from @temp as oldT join @tempIds as newT on oldT.rownumber = newT.rownumber insert into dbo.Table_2(Table1Id) select ftemp.new_id from dbo.Table_2 t2 join @finalTemp ftemp on ftemp.OldId = t2.Table1Id select * from dbo.Table_1 select * from dbo.Table_2