Post reply

T-sql how to fine inserted record id

  • April 8, 2009 at 9:58 pm

    #202675

    hi,

    I have table

    create table temp(id int primary key auto_increment(1,1),name varchar(100))

    now is it possible to find what value incremented the table with column name "Id"..

    example.

    suppose i inserted one record

    id name

    1 giri

    2 james

    i inserted no of records and deleted some one.now i want to know

    what id incremented that table..

    regards:

    Dastagiri.D

    Thanks
    Dastagiri.D

  • April 8, 2009 at 11:03 pm

    #974833

    Use IDENT_CURRENT(Table Name) function to know the recent identity value of that table.

  • April 8, 2009 at 11:23 pm

    #974838

    To know the current identity value use IDENT_CURRENT function. To know the increment value use the IDENT_INCR function. To know the original value of the seed use the IDENT_SEED function.

    "Keep Trying"

  • April 9, 2009 at 5:52 am

    #974946

    During the insert you have a couple of options. You can use SCOPE_IDENTITY if you are inserting a single row at a time. You can use the OUTPUT clause if you are inserting mutliple rows.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 9, 2009 at 5:53 am

    #974947

    thanks...

    but what I need suppose I inserted 1 to 10 values in a table

    and deleted 5 to 10 ..next i will insert record 11 is the id values..

    I want to find that id(11) value with any t-sql code..

    regards:

    Dastagiri.D

    Thanks
    Dastagiri.D

  • April 9, 2009 at 5:57 am

    #974949

    thanks for this..

    it send inserted record id .i wont insert any record ..but i need to find id value what we are going to insert the database table

    regards

    Dastagiri

    Thanks
    Dastagiri.D

  • April 9, 2009 at 5:57 am

    #974950

    thanks...

    but what I need suppose I inserted 1 to 10 values in a table

    and deleted 5 to 10 ..next i will insert record 11 is the id values..

    I want to find that id(11) value with any t-sql code..

    Thanks
    Dastagiri.D

  • April 9, 2009 at 6:04 am

    #974956

    dastagirid (4/9/2009)

    thanks...

    but what I need suppose I inserted 1 to 10 values in a table

    and deleted 5 to 10 ..next i will insert record 11 is the id values..

    I want to find that id(11) value with any t-sql code..

    If you want to know the value that the next row is going to use BEFORE you do the insert, then Chirag and Reddy have already supplied you with what you need.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 9, 2009 at 6:09 am

    #974961

    does he mean something like @@identity as well?

  • April 9, 2009 at 6:46 am

    #974987

    I don't think so. Plus, I just wouldn't use @@IDENTITY. It's too prone to error. SCOPE_IDENTITY is much more consistent and I'm not aware of anyone having errors with it like the good ole' days using @@IDENTITY.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 9, 2009 at 8:24 am

    #975083

    PRINT IDENT_CURRENT('table')

    This will give the most recent IDENTITY value for 'table' - regardless of whether you created it or not

    PRINT SCOPE_IDENTITY()

    This will give the IDENTITY value last created within the current stored procedure, trigger, etc.

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply