CODEWITHSUNDEEP
sql-servert-sql
BradDaBug
BradDaBug

Reputation: 101

Insert parent records and update linked children

This is such a simple problem, but I cannot figure out the syntax to do this. I'm doing some data migration, and I have some existing records in a table that I want to link to a brand new parent table.

Let's say I have two tables:

declare @Parent table
(
    ID int identity(1,1),
    DummyField int
)

declare @Child table
(
    ID int identity(1,1),
    ParentID int, -- assume this is a new column, all nulls
    DummyField int
)

For each existing Child record, insert a new parent record and update Child to link to it.

That's it. It's so simple, but I can't figure it out without using a cursor.

Upvotes: 0

Views: 458

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

relation betwwen child and parent is missing

you hv to use output clause to achive so,

try like this,

declare @output table (parentid int)
insert into parent (parentid)
output inserted.parentid into @output
-- values


update c 
set ParentID = o.parentid
from children c
inner join @output o on c.parentid=o.parentid

Upvotes: 0

War
War

Reputation: 8628

Seudo SQL logic seems to be pretty simple ...

insert in to parent.

update childern 
set ParentID = select @@IDENTITY
where ParentID = @previousParentId

unless I missed something here?

Upvotes: 1

Related Questions