How to get the identity of an inserted row?

When you use Entity Framework, it internally uses the `OUTPUT` technique to return the newly inserted ID value

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID INTO @generated_keys
VALUES('Malleable logarithmic casing');

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

The output results are stored in a temporary table variable, joined back to the table, and return the row value out of the table.

Note: 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.

This technique (`OUTPUT`) is only available on SQL Server 2008 or newer.

**Edit** - The reason for the join
------------

The reason that Entity Framework joins back to the original table, rather than simply use the `OUTPUT` values is because EF also uses this technique to get the `rowversion` of a newly inserted row.

You can use optimistic concurrency in your entity framework models by [using the `Timestamp` attribute:][1] <sup>[🕗][2]</sup>

public class TurboEncabulator
{
public String StatorSlots)

[Timestamp]
public byte[] RowVersion { get; set; }
}

When you do this, Entity Framework will need the `rowversion` of the newly inserted row:

DECLARE @generated_keys table([Id] uniqueidentifier)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID INTO @generated_keys
VALUES('Malleable logarithmic casing');

SELECT t.[TurboEncabulatorID], t.[RowVersion]
FROM @generated_keys AS g
JOIN dbo.TurboEncabulators AS t
ON g.Id = t.TurboEncabulatorID
WHERE @@ROWCOUNT > 0

And in order to retrieve this `Timetsamp` you **cannot** use an `OUTPUT` clause.

That's because if there's a trigger on the table, any `Timestamp` you OUTPUT will be wrong:

- Initial insert. Timestamp: 1
- OUTPUT clause outputs timestamp: 1
- trigger modifies row. Timestamp: 2

The returned timestamp will **never** be correct if you have a trigger on the table. So you **must** use a separate `SELECT`.

And even if you were willing to suffer the incorrect rowversion, the other reason to perform a separate `SELECT` is that you cannot OUTPUT a `rowversion` into a table variable:

DECLARE @generated_keys table([Id] uniqueidentifier, [Rowversion] timestamp)

INSERT INTO TurboEncabulators(StatorSlots)
OUTPUT inserted.TurboEncabulatorID, inserted.Rowversion INTO @generated_keys
VALUES('Malleable logarithmic casing');

The third reason to do it is for symmetry. When performing an `UPDATE` on a table with a trigger, you **cannot** use an `OUTPUT` clause. Trying do `UPDATE` with an `OUTPUT` is not supported, and will give an error:

-

[To see links please register here]

The only way to do it is with a follow-up `SELECT` statement:

UPDATE TurboEncabulators
SET StatorSlots = 'Lotus-O deltoid type'
WHERE ((TurboEncabulatorID = 1) AND (RowVersion = 792))

SELECT RowVersion
FROM TurboEncabulators
WHERE @@ROWCOUNT > 0 AND TurboEncabulatorID = 1


[1]:

[To see links please register here]

[2]:

[To see links please register here]

Create a `uuid` and also insert it to a column. Then you can easily identify your row with the uuid. Thats the only 100% working solution you can implement. All the other solutions are too complicated or are not working in same edge cases.
E.g.:

**1) Create row**

INSERT INTO table (uuid, name, street, zip)
VALUES ('2f802845-447b-4caa-8783-2086a0a8d437', 'Peter', 'Mainstreet 7', '88888');

**2) Get created row**

SELECT * FROM table WHERE uuid='2f802845-447b-4caa-8783-2086a0a8d437';

Even though this is an older thread, there is a newer way to do this which avoids some of the pitfalls of the IDENTITY column in older versions of SQL Server, [like gaps in the identity values after server reboots][1]. Sequences are available in SQL Server 2016 and forward which is the newer way is to create a SEQUENCE object using TSQL. This allows you create your own numeric sequence object in SQL Server and control how it increments.

Here is an example:

CREATE SEQUENCE CountBy1
START WITH 1
INCREMENT BY 1 ;
GO

Then in TSQL you would do the following to get the next sequence ID:

SELECT NEXT VALUE FOR CountBy1 AS SequenceID
GO

Here are the links to [CREATE SEQUENCE][2] and [NEXT VALUE FOR][3]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

I believe the safest and most accurate method of retrieving the inserted id would be using the output clause.

for example (taken from the following [MSDN][1] article)

> USE AdventureWorks2008R2;
> GO
> DECLARE @MyTableVar table( NewScrapReasonID smallint,
> Name varchar(50),
> ModifiedDate datetime);
> INSERT Production.ScrapReason
> OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
> INTO @MyTableVar
> VALUES (N'Operator error', GETDATE());
>
> --Display the result set of the table variable.
> SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
> --Display the result set of the table.
> SELECT ScrapReasonID, Name, ModifiedDate
> FROM Production.ScrapReason;
> GO



[1]:

[To see links please register here]

From MSDN

> @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.
>
> @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
>
> IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

- IDENT_CURRENT is a function which takes a table as a argument.
- @@IDENTITY may return confusing result when you have an trigger on the table
- SCOPE_IDENTITY is your hero most of the time.

Complete solution in SQL and ADO.NET



const string sql = "INSERT INTO [Table1] (...) OUTPUT INSERTED.Id VALUES (...)";

using var command = connection.CreateCommand();
command.CommandText = sql;
var outputIdParameter = new SqlParameter("@Id", SqlDbType.Int) { Direction = ParameterDirection.Output };
command.Parameters.Add(outputIdParameter);

await connection.OpenAsync();

var outputId= await command.ExecuteScalarAsync();

await connection.CloseAsync();

int id = Convert.ToInt32(outputId);