When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
Sql-server – How to extract the last inserted row in SQL Server
identityinsertsql servert-sql
Related Question
- Sql-server – Get identity value of all rows inserted
- Sql-server – Are auto incrementing identities always given in the order of inserts in SQL Server
- Mysql – How to prevent multiple row insert
- Sql-server – After insert trigger only using single inserted virtual table row
- Sql-server – How to retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement
- Postgresql – How to obtain the last inserted row with INSERT … SELECT in PostgreSQL
Best Answer
By definition, a table is an unordered bag of rows (see #3 here). There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an
IDENTITY
column, you can useSCOPE_IDENTITY()
(never use@@IDENTITY
, since that can be unreliable if you have or will ever add triggers to the source table):More generally, you can use the
OUTPUT
clause, which doesn't rely on anIDENTITY
column (but will still make it difficult to identify which row(s) the clause identifies if there is no PK):If you're not talking about the same batch, then the only real way to identify the last row inserted is to use a date/time column where the timestamp of insertion is recorded. Otherwise it is like you emptied a bag of marbles on the floor, then asked someone to enter the room and identify which one hit the floor last.
You may be tempted or even advised to use the
IDENT_CURRENT()
function, but I explain here why this is unreliable too.You could add a column to track this going forward:
Now you can find the last row(s) inserted by simply:
(If you don't want ties, you can add a tie-breaking column to the
ORDER BY
, or you can simply changeRANK()
toROW_NUMBER()
if you don't care which of the tied rows you get.)You might make the assumption that the last row inserted is the highest identity value, but this isn't necessarily the case. The identity can be reseeded and it can also be overridden using
SET IDENTITY_INSERT ON;
.