Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Exctract it for what? Generally - SQL tables have no concept of "last inserted rows", a rows is a row. After the insert it is just there. If you need to know the last inserted, your table must facilitate this (with a timestamp, with an autoincrement etc.). –  TomTom Nov 10 '13 at 7:57
2  
You can output what is being inserted in a INSERT statement using the OUTPUT clause - but after that, once the row has been inserted, then you'll need to either have a date/time column that you keep track of when a row was inserted, or you need an ever-increasing IDENTITY column that allows you to get the last inserted row –  marc_s Nov 10 '13 at 13:03
2  
Assuming there's a field (or fields) that lets you determine which row is "last," it's easy to "extract" it using a trigger. What do you want to do with the row? –  Jon Seigel Nov 10 '13 at 15:54
3  
You need to help us define what you mean by "last"... If you say INSERT ... VALUES(c),(b),(a); do you mean a? If you say INSERT ... SELECT cols FROM dbo.othertable; what does "last" mean then? Can you tell us a little more about your table (does it have an identity column or a CreatedDate column that gets populated by default)? What if there is a tie (5 rows inserted at the same time) - do you want one row or all 5? At what point do you want to extract the last inserted row? Immediately after the insert by the same user, or at some later time? –  Aaron Bertrand Nov 10 '13 at 16:32
    
@AaronBertrand Yes I wanted the last row inserted for the entire table, not just a batch insert. Its not possible as per the comments and the answer directly as such –  user2493976 Nov 11 '13 at 5:34

1 Answer 1

up vote 3 down vote accepted

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 IDENTITY column, you can say:

INSERT dbo.table(column) SELECT 1;
SELECT SCOPE_IDENTITY();

More generally, you can use the OUTPUT clause, which doesn't rely on an IDENTITY column (but will still make it difficult to identify which row(s) the clause identifies if there is no PK):

INSERT dbo.table(column) OUTPUT inserted.* SELECT 1;

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:

ALTER TABLE dbo.table ADD DateInserted DEFAULT CURRENT_TIMESTAMP;

Now you can find the last row(s) inserted by simply:

;WITH x AS (SELECT *, r = RANK() OVER (ORDER BY DateInserted DESC)
   FROM dbo.table)
SELECT * FROM x WHERE r = 1;

(If you don't want ties, you can add a tie-breaking column to the ORDER BY, or you can simply change RANK() to ROW_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;.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.