Posted on 2011-10-13
Last Modified: 2012-05-12

How to select Last inserted ID ?

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

Question by:hrvica5
    LVL 8

    Expert Comment

    If ID column is Unique ID then you can get last inserted id using @@IDENTITY.

    Author Comment

    yes id is unique

    LVL 7

    Expert Comment

    LVL 69

    Expert Comment

    by:Éric Moreau
    just after the insert statement, you can use SELECT SCOPE_IDENTITY()

    if not, you will have to use: select max(ID) from table
    LVL 5

    Accepted Solution

    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 = (select max( from table as b)

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now