Reference generated primary key in SQL script

Question

I'm trying to create a bunch of entries in a database with a single script and the problem I'm encountering is how to reference the generated primary key of the previous entry I created.

For example if I created a customer, then tried to create an order for that customer, how do I get the primary key generated for the customer?

I'm using SQLServer.

Spencer Ruport · Accepted Answer · 2009-01-16 01:31:34Z

4

Like so:

DECLARE @customerid int;
INSERT INTO customers(name) VALUES('Spencer');
SET @customerid = @@IDENTITY;

EDIT:

Apparently it needs to be SCOPE_IDENTITY() in order to function as expected with triggers.

DECLARE @customerid int;
INSERT INTO customers(name) VALUES('Spencer');
SET @customerid = SCOPE_IDENTITY();

edited Jan 16, 2009 at 1:31

answered Jan 16, 2009 at 0:57

Spencer Ruport

35.2k12 gold badges89 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joel Coehoorn Over a year ago

I believe scope_identity() is preferred to @@IDENTITY in most situations.

Sam Saffron Over a year ago

+1 for Joels note, scoped_identity will handle triggers gracefully (eg. if you have an insert trigger, your @@identity can become bogus)

Kenny Over a year ago

That did the trick thanks, I'm using SQLServer 2005 which does seem to support scope_identity().

kristof Over a year ago

Kenny, scope_identity() is supported by SQLServer 2005 and even 2000. It is safer to use than @@identity.

beach · Accepted Answer · 2009-01-16 01:03:42Z

4

If available in your version, use SCOPE_IDENTITY() instead. Safer than @@IDENTITY.

answered Jan 16, 2009 at 1:03

beach

8,7004 gold badges34 silver badges26 bronze badges

Comments

Cade Roux · Accepted Answer · 2009-01-16 01:40:09Z

2

If you are inserting multiple rows at once, you can get all the identities (for use in, say, creating related records) by using the OUTPUT INTO feature of SQL Server 2005 or later.

This could avoid you having to write loops and cursors etc.

answered Jan 16, 2009 at 1:40

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Collectives™ on Stack Overflow

Reference generated primary key in SQL script

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related