CODEWITHSUNDEEP
sqlsql-servert-sqlcopy
Simon Lemoine
Simon Lemoine

Reputation: 23

Copy entries from table1 then copy entries from table2 with table1 foreign keys

I am trying to copy data from different tables while keeping their different link between each other.

Simplified structures of my tables:

timetable (pk INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(50))
timetable_group (pk INT PRIMARY KEY IDENTITY(1,1), timetable_fk INT, name VARCHAR(50))
timetable_class (pk INT PRIMARY KEY IDENTITY(1,1), timetable_fk INT, timetable_group_fk INT, name VARCHAR(50))

My aim is to be able to copy an entry of timetable with all of its related entries within timetable_group and timetable_class.

After getting the @new_timetable_fk with an INSERT and @@IDENTITY, I hit my problem:

How to copy from timetable_group and get back the inserted pks to be able to use them when copying timetable_class ?

I tried using this:

-- This is a parameter to my code
DECLARE @timetable_fk INT
SET @timetable_fk = 1

-- start of my Stored Procedure

-- this will hold my new timetable_fk
DECLARE @new_timetable_fk INT

-- this will hold the old and new pks of timetable_group
-- so we can join on it
DECLARE @timetable_group_link_pks TABLE (new_pk INT, old_pk INT)

-- copy the timetable entry
INSERT INTO timetable (name)
SELECT LEFT(name + ' copy', 50)
FROM timetable
WHERE pk = @timetable_fk

-- get the pk of the newly inserted value
SET @new_timetable_fk = @@IDENTITY


-- copy timetable_group
INSERT INTO timetable_group (timetable_fk, name)
-- This return the error `The multi-part identifier "g.pk" could not be bound` 
OUTPUT inserted.pk, g.pk
    INTO @timetable_group_link_pks
SELECT @new_timetable_fk, g.name
FROM timetable_group g
WHERE g.timetable_fk = @timetable_fk

-- copy timetable class
INSERT INTO timetable_class (timetable_fk, timetable_group_fk, name)
SELECT @new_timetable_fk, gl.new_pk, c.name
FROM timetable_class c
    JOIN @timetable_group_link_pks gl ON c.timetable_group_fk = gl.old_pk
WHERE c.timetable_fk = @timetable_fk

But it does not work as I cannot use g.pk within the OUTPUT clause.

MSDN tells us that the OUTPUT clause is supposed to work as I want, but TSQL does not accept it.

Any idea how to solve this problem?

Upvotes: 0

Views: 81

Answers (2)

jokedst
jokedst

Reputation: 344

The g.pk doesn't exist in the "output" because "inserted" and the select query are not automatically joined.

You can just skip the @timetable_group_link_pks table and the OUTPUT stuff, you don't need it.

This should work:

-- copy timetable_group
INSERT INTO timetable_group (timetable_fk, name)
SELECT @new_timetable_fk, g.name
FROM timetable_group g
WHERE g.timetable_fk = @timetable_fk

-- copy timetable_class
INSERT INTO timetable_class (timetable_fk, timetable_group_fk, name)
SELECT @new_timetable_fk, c.timetable_group_fk, c.name
FROM timetable_class c
join timetable_group g on g.timetable_fk = c.timetable_fk
WHERE c.timetable_fk = @timetable_fk and g.pk = @timetable_fk

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 1541

you should use Merge statement along with Output clause instead of insert directly, please refer Link

Upvotes: 2

Related Questions