How to get Last inserted row in SQL 2014

Question

How to get Last inserted row in SQL 2014

durai raj s 20

In my SQL Table having more than 100000 Rows , Every 1 Min the new row is inserted into the Table.How to get Last inserted row

I planned for Trigger Query based on the Last inserted row

3 answers

Your answer

Answer 1

LiHongMSFT-4306 31,566

Hi @durai raj s

If there is an identity column in this table, then you could use IDENT_CURRENT('tableName') which returns the last identity value generated for a specific table in any session and any scope.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

durai raj s 20 Reputation points

2023-03-26T08:31:38.12+00:00

Can you share some example query for fetch last inserted row data ?
durai raj s 20 Reputation points

2023-03-26T08:32:10.09+00:00

Can you share some example query for fetch last inserted row data ?

LiHongMSFT-4306 31,566

Check this sample:

CREATE TABLE #TEMP(ID INT IDENTITY(1,1),Val VARCHAR(20),InsertTime DATETIME DEFAULT CURRENT_TIMESTAMP)
INSERT INTO #TEMP(Val) VALUES ('AAA');
INSERT INTO #TEMP(Val) VALUES ('BBB');
INSERT INTO #TEMP(Val) VALUES ('CCC');

--If you have IDENTITY column in your table
SELECT * FROM #TEMP
WHERE ID=IDENT_CURRENT('#TEMP')

--If you have self-increase column ie:TIMESTAMP
SELECT TOP(1)* 
FROM #TEMP
ORDER BY InsertTime DESC

DROP TABLE #TEMP

LiHongMSFT-4306 31,566 Reputation points

2023-03-31T09:29:20.9866667+00:00

Hi @durai raj s

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

You need some column in the table that can identify the row as the most recent. If you don't have that, you can't identify the row. A table is by definition an unordered set.

durai raj s 20 Reputation points

2023-03-26T08:30:59.5633333+00:00

Can you share some example query for fetch last inserted row data ?
durai raj s 20 Reputation points

2023-03-26T08:32:32.1633333+00:00

Can you share some example query for fetch last inserted row data ?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-03-26T12:11:02.2166667+00:00

Can you share some example query for fetch last inserted row data ?

If you share your table definition and tell us something about it - maybe. Again, there must be something in the data that identifies the row as "last inserted". If there is not - you can't do it.

Answer 3

In addition to the above responses, i.e. in addition to maintain and identity column, another option to add a field of type DateTime that stores the inserted date and time. You could create the column to the table, and add a default value for that column as getdate()

Now, when you insert a new row, automatically the inserted time will be recorded in the field. Now you can use the select clause with order by to retrieve the data in the order of the date field you just created.

Hope this helps

Share via

How to get Last inserted row in SQL 2014

3 answers

Your answer