[sql] SQL Server - Return value after INSERT

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.

The best and most sure solution is using SCOPE_IDENTITY().

Just you have to get the scope identity after every insert and save it in a variable because you can call two insert in the same scope.

ident_current and @@identity may be they work but they are not safe scope. You can have issues in a big application

  declare @duplicataId int
  select @duplicataId =   (SELECT SCOPE_IDENTITY())

More detail is here Microsoft docs

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

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

@@IDENTITY Is a system function that returns the last-inserted identity value.

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.

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

This is how I use OUTPUT INSERTED, when inserting to a table that uses ID as identity column in SQL Server:

'myConn is the ADO connection, RS a recordset and ID an integer
Set RS=myConn.Execute("INSERT INTO M2_VOTELIST(PRODUCER_ID,TITLE,TIMEU) OUTPUT INSERTED.ID VALUES ('Gator','Test',GETDATE())")
ID=RS(0)

* Parameter order in the connection string is sometimes important. * The Provider parameter's location can break the recordset cursor after adding a row. We saw this behavior with the SQLOLEDB provider.

After a row is added, the row fields are not available, UNLESS the Provider is specified as the first parameter in the connection string. When the provider is anywhere in the connection string except as the first parameter, the newly inserted row fields are not available. When we moved the the Provider to the first parameter, the row fields magically appeared.

You can append a select statement to your insert statement. Integer myInt = Insert into table1 (FName) values('Fred'); Select Scope_Identity(); This will return a value of the identity when executed scaler.

There are many ways to exit after insert

When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that’s been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application. Use the OUTPUT INTO form if you want to return the data to a table or a table variable.

DECLARE @MyTableVar TABLE (id INT,NAME NVARCHAR(50));

INSERT INTO tableName
(
  NAME,....
)OUTPUT INSERTED.id,INSERTED.Name INTO @MyTableVar
VALUES
(
   'test',...
)

IDENT_CURRENT: It returns the last identity created for a particular table or view in any session.

SELECT IDENT_CURRENT('tableName') AS [IDENT_CURRENT]

SCOPE_IDENTITY: It returns the last identity from a same session and the same scope. A scope is a stored procedure/trigger etc.

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];  

@@IDENTITY: It returns the last identity from the same session.

SELECT @@IDENTITY AS [@@IDENTITY];

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

There are multiple ways to get the last inserted ID after insert command.

1. @@IDENTITY : It returns the last Identity value generated on a Connection in current session, regardless of Table and the scope of statement that produced the value
2. SCOPE_IDENTITY(): It returns the last identity value generated by the insert statement in the current scope in the current connection regardless of the table.
3. IDENT_CURRENT(‘TABLENAME’) : It returns the last identity value generated on the specified table regardless of Any connection, session or scope. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

Now it seems more difficult to decide which one will be exact match for my requirement.

I mostly prefer SCOPE_IDENTITY().

If you use select SCOPE_IDENTITY() along with TableName in insert statement, you will get the exact result as per your expectation.

Source : CodoBee