SQL Server - Return value after INSERT

Question

I'm trying to get a the key-value back after an INSERT-statement. Example: I've got a table with the attributes name and id. id is a generated value.

    INSERT INTO table (name) VALUES('bob');

Now I want to get the id back in the same step. How is this done?

We're using Microsoft SQL Server 2008.

Answer 1

You can use scope_identity to select the ID of the row you just inserted into a variable then just select whatever columns you want from that table where the id = the identity you got from scope_identity

See here for the MSDN info http://msdn.microsoft.com/en-us/library/ms190315.aspx

Answer 2

Entity Framework performs something similar to gbn's answer:

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO Customers(FirstName)
OUTPUT inserted.CustomerID INTO @generated_keys
VALUES('bob');

SELECT t.[CustomerID]
FROM @generated_keys AS g 
   JOIN dbo.Customers AS t 
   ON g.Id = t.CustomerID
WHERE @@ROWCOUNT > 0

The output results are stored in a temporary table variable, and then selected back to the client. Have to be aware of the gotcha:

inserts can generate more than one row, so the variable can hold more than one row, so you can be returned more than one ID

I have no idea why EF would inner join the ephemeral table back to the real table (under what circumstances would the two not match).

But that's what EF does.

SQL Server 2008 or newer only. If it's 2005 then you're out of luck.

Answer 3

Use SCOPE_IDENTITY() to get the new ID value

INSERT INTO table (name) VALUES('bob');

SELECT SCOPE_IDENTITY()

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Answer 4

After doing an insert into a table with an identity column, you can reference @@IDENTITY to get the value: http://msdn.microsoft.com/en-us/library/aa933167%28v=sql.80%29.aspx

Answer 5

INSERT INTO files (title) VALUES ('whatever'); 
SELECT * FROM files WHERE id = SCOPE_IDENTITY();

Is the safest bet since there is a known issue with OUTPUT Clause conflict on tables with triggers. Makes this quite unreliable as even if your table doesn't currently have any triggers - someone adding one down the line will break your application. Time Bomb sort of behaviour.

See msdn article for deeper explanation:

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults.aspx