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

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)

Answer 2

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. @identity 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

Answer 3

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.

Answer 4

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

Answer 5

* 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.

Answer 6

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too