Insert a record and return new key?

[Machaira]

OK, I'm assuming there's a way to do this in SQL Server but I've never needed to do it before and can't 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? I'm 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]

I'll 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. :)