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.

Answer 1

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();

Answer 2

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

Answer 3

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.