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

User · Answer

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];

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

There are multiple ways to get the last inserted ID after insert command      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 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  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server - Return value after INSERT

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008