Results 1 to 4 of 4

Thread: Identity Field

  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: Identity Field

    Folks

    I am inserting some values into a table with the following stmt

    Insert into table(number,name) values ('12','name')

    In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
    of the ID (the most recent one) in the next select statement.

    ie Select @@identity from table (any condition????)

    How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.


    Thanks for the help,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is only one @@identity tracked for any given connection to MS-SQL (each spid). To retrieve its value, you just select it (no table needed). Something like:
    PHP Code:
    DECLARE @id INT
    INSERT INTO HHGtable 
    (theAnswerVALUES (43)  // whatever
    SELECT @id = @@identity 
    -PatP

  3. #3
    Join Date
    May 2003
    Posts
    40

    Re: Identity Field

    Pat

    Thanks for the idea. BTW I have a question can I use

    select Max(ID) from table

    So that It gives only the maximum value and it would be same value
    when the record is inserted? I am just asking your suggestion. Is that logically correct??


    Thanks for the help,

  4. #4
    Join Date
    Feb 2004
    Posts
    492

    Re: Identity Field

    I would think so, but only as long as the tables next identity never is reset to a lower value (dbcc checkident is able to do so).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •