CODEWITHSUNDEEP
sql-server
blue piranha
blue piranha

Reputation: 3876

Getting GUID of recently inserted row

Consider these two SQL Server tables

My user interface takes the person name and list of hobbies as input. When user submits the form, I would like to insert appropriate records in the two tables. For e.g.

 insert into Person 
 values(NEWID(), 'John')

I wanted to know how can I get the value of this GUID so that I can do the insert into the Hobby table?

 insert into Hobby(PersonId, HobbyName) 
 values(WHAT???, 'Ping-pong')

There could be several John in the table. How can I get the GUID of this particular John?

Upvotes: 1

Views: 622

Answers (1)

techspider
techspider

Reputation: 3408

One way is to pre-generate your GUID and use that in all your queries. It is the way I used in many of the places I required.

DECLARE @personId UNIQUEIDENTIFIER
SET @personId = NEWID()

insert into Person values(@personId , 'John')
insert into Hobby(PersonId, HobbyName) values(@personId, 'Ping-pong')

OR

You can have a temporary table to output the inserted value and use it in further queries assuming the below statements execute only once in one single call.

DECLARE @myIdentifier TABLE (
    InsertedGuid UNIQUEIDENTIFIER
)

insert into Person 
output inserted.PersonId into @myIdentifier 
values(NEWID(), 'John')

insert into Hobby(PersonId, HobbyName) 
values(SELECT InsertedGuid FROM @myIdentifier, 'Ping-pong')

Upvotes: 2

Related Questions