Returning an identity after insert, then using identity value in another insert

Question

To preface, I have to do a bulk insert in a patients table that looks similar to:

pid     | FirstName       | LastName
-------------------------------------------
10     | John             | Cena
11     | Eric             | Bischoff
12     | Vince            | Russo

My insert statement looks something like:

INSERT INTO patients (FirstName, LastName) 
OUTPUT inserted.pid
VALUES ('Seth', Rollins');

This returns the latest pid, but how would I use that returned identity in another insert statement in a completely different table?

INSERT INTO booking (pid, start, end) 
VALUES (inserted.pid, 'XX/XX/XXXX', XX/XX/XXXX');

IE:

booking_id | pid   | start       | end 
-----------------------------------------
1          | 10    | 08/01/2016  | NULL
2          | 11    | 07/25/2016  | 07/26/2016

I am looping through a large .csv file with this first and last name information.

If you're only inserting a single value at a time you can use Scope_Identity() to get the last identity value created. https://msdn.microsoft.com/en-us/library/ms190315.aspx — Phritzy, Aug 09 '16 at 18:31
Possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — techspider, Aug 09 '16 at 18:32

Paweł Dyl · Accepted Answer · 2016-08-09T19:13:09.780

1

If you don't want to use SCOPE_IDENTITY(), you can use following syntax:

DECLARE @out TABLE (pid int);

INSERT INTO patients (FirstName, LastName) 
OUTPUT inserted.pid INTO @out
VALUES ('Seth', 'Rollins');

INSERT INTO booking (pid, start, end) 
VALUES ((SELECT TOP 1 pid FROM @out), 'XX/XX/XXXX', 'XX/XX/XXXX');

edited Aug 09 '16 at 19:13

answered Aug 09 '16 at 18:48

Paweł Dyl

8,888
1
11
27

Thanks @Pawel Dyl, but your solution returns this result: A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement. Msg 137, Level 15, State 2, Line 25 Must declare the scalar variable "@lastPid". – etm124 Aug 09 '16 at 18:57

score 0 · Answer 2 · answered Aug 09 '16 at 18:29

The most transtworthy method will be:

declare @ID table (ID int)
declare @someInt int

INSERT INTO patients (FirstName, LastName) 
OUTPUT inserted.pid into into @ID
VALUES ('Seth', 'Rollins');

SELECT @someInt = ID FROM @ID

INSERT INTO booking (pid, start, end) 
VALUES (@someInt , 'XX/XX/XXXX', XX/XX/XXXX');

Returning an identity after insert, then using identity value in another insert

2 Answers2