SQL Copy row in a table with relations

Question

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.

Have a look at [Using merge..output to get mapping between source.id and target.id](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) — Mikael Eriksson, Nov 27 '12 at 06:48

Khasha · Accepted Answer · 2020-10-07T04:26:19.247

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

SQL Copy row in a table with relations

1 Answers1