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
x
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