[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:



How to select Last inserted ID ?

'Select * from table where'' last inserted id???

1 Solution
If ID column is Unique ID then you can get last inserted id using @@IDENTITY.
hrvica5Author Commented:
yes id is unique

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Éric MoreauSenior .Net ConsultantCommented:
just after the insert statement, you can use SELECT SCOPE_IDENTITY()

if not, you will have to use: select max(ID) from table
if you are wanting the value directly after it is inserted then you can use select @@identity although in a high volume transactional system this can be dangerous.

 If it is you doing the insert you can get the value using the OUTPUT keyword as part of the insert (look this up in BOL)

if all else fails you can simple do:

select a.* from table as a where a.id = (select max(b.id) from table as b)

Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, @@identity is only to be used when
1) you are using SQL 7
2) you are in the same session that the one that did that "last insert"

for sql 2000 and later, you shall use SCOPE_IDENTITY() , but 2) above still applies.


INSERT INTO yourtable ( col1, col2... ) VALUES ( 1,2, ... )
SELECT * FROM yourtable WHERE ID_col = @@identity -- sql 7
SELECT * FROM yourtable WHERE ID_col = SCOPE_IDENTITY() -- sql 2000+

note: while the function @@identity still exists in sql 2000+, it does though refer to the last identity value generated, which might not be the table you inserted into, but generated by a insert that is run by a trigger on the table you inserted to ...

to conclude: if you want to get the last generated record, even if the application did not do the insert itself:

select * from yourtable where id_col = ( Select max(id_col) from yourtable )
select top 1 * from yourtable order by id_col desc

hope this clarifies

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now