CODEWITHSUNDEEP
sqlsql-server
mar_sanbas
mar_sanbas

Reputation: 943

Sql Server - How to get last id inserted into table

I'm trying to get the last id inserted into a table. I was using 

SELECT IDENT_CURRENT('TABLE')

But the problem is that it doesn't return the last inserted id, it returns the max inserted id.

For example, if i do:

INSERT INTO 'TABLA' (ID) VALUES (100)

SELECT IDENT_CURRENT('TABLE') returns 100

but then if i do

INSERT INTO 'TABLA' (ID) VALUES (50)

SELECT IDENT_CURRENT('TABLE') returns 100

and I want to get 50

I need the ID of a specific table, and I generate the id dinamically, so it's not an identity How can i do it?

Upvotes: 0

Views: 7231

Answers (3)

M.Ali
M.Ali

Reputation: 69494

SELECT SCOPE_IDENTITY()

will return the last value inserted in current session.
Edit
Then what you are doing is the best way to go just make sure that the ID Column is an IDENTITY Column, IDENT_CURRENT('Table_name'), @@IDENTITY and SCOPE_IDENTITY() returns last value generated by the Identity column.
If the ID column is not an Identity Column, all of these functions will return NULL.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SQL Server does not keep track of the last value inserted into an IDENTITY column, particularly when you use SET IDENTITY_INSERT ON;. But if you are manually specifying the value you are inserting, you don't need SQL Server to tell you what it is. You already know what it is, because you just specified it explicitly in the INSERT statement.

If you can't get your code to keep track of the value it just inserted, and can't change the table to have a DateInserted column with a default of CURRENT_TIMESTAMP (which would allow you to see which row was inserted last), perhaps you could add a trigger to the table that logs all inserts.

Upvotes: 1

Phil Sandler
Phil Sandler

Reputation: 28016

From your code, it looks like ID is not an identity (auto-increment) column, so IDENT_CURRENT isn't going to do what you are expecting.

If you want to find the last row inserted, you will need a datetime column that represents the insert time, and then you can do something like:

SELECT TOP 1 [ID] FROM TABLEA ORDER BY [InsertedDate] DESC

Edited: a few additional notes:

  • Your InsertedDate column should have a default set to GetDate() unless your application, stored procs or whatever you use to perform inserts will be responsible for setting the value
  • The reason I said your ID is not an identity/auto-increment is because you are inserting a value into it. This is only possible if you turn identity insert off.

Upvotes: 5

Related Questions