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?
By definition, a table is an unordered set of rows. 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
More generally, you can use the
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 asking someone to enter the room and identify which one hit the floor last. So for example, 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
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