Insert a record and return new key?

[Machaira]

OK, Im assuming theres a way to do this in SQL Server but Ive never needed to do it before and cant seem to find the exact syntax. I need to create a new record in a table and get back the key field that was created. Can I do this with one statement?

 

[PlausiblyDamp]

After doing an insert you can always get the value from @@IDENTITY

 

[Machaira]

Can you elaborate on this? How would the SQL look? Im a relative newb in SQL Server.

Thanks for the reply.

 

[PlausiblyDamp]

If you run the following against Northwind you should see what I mean.

INSERT Employees (FirstName, LastName) VALUES (test, Test)

SELECT @@IDENTITY

 

[Machaira]

Ill give it a try. Thanks much!

 

[APaule]

INSERT Employees (FirstName, LastName) VALUES (test, Test)

SELECT @@IDENTITY

Be careful with @@IDENTITY. This system variable allways contains the last inserted key of the executed command in the actual session. This means, if the insert triggers another insert into a table with an identity column, it will return that key.

Better use the function SCOPE_IDENTITY(). It returns the key only out of the actual scope in the actual session. You use it the same way:

INSERT Employees (FirstName, LastName) VALUES (test, Test);
SELECT SCOPE_IDENTITY()

 

[michael_hk]

APaule, thanks for sharing. I always used @@IDENTITY before.