Results 1 to 4 of 4

Thread: best way to retrieve identity after insert

  1. #1
    bigpow Guest

    best way to retrieve identity after insert

    I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
    Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
    Thanks in advance. -bigpow

  2. #2
    patrick beagan Guest

    best way to retrieve identity after insert (reply)


    Can you do this programmitically in VB?

    ------------
    Brian J. Smith at 5/3/99 4:06:42 PM

    Check BOL and @@identity.


    ------------
    bigpow at 4/30/99 7:57:42 PM

    I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
    Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
    Thanks in advance. -bigpow

  3. #3
    Brian J. Smith Guest

    best way to retrieve identity after insert (reply)

    If you're working with SPs, use output parameters.

    create procedure TestID(@ID int output)
    as
    insert into ident_tab
    values('Test&#39

    select @ID = @@Identity

    return
    GO

    ...Then in VB, when you bind to the SP, define the parameter as rdParamOutput, or adParamOutput (depending on whether or not you're using ADO or RDO.)


    ------------
    patrick beagan at 5/4/99 12:54:55 PM


    Can you do this programmitically in VB?

    ------------
    Brian J. Smith at 5/3/99 4:06:42 PM

    Check BOL and @@identity.


    ------------
    bigpow at 4/30/99 7:57:42 PM

    I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
    Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
    Thanks in advance. -bigpow

  4. #4
    Guest

    best way to retrieve identity after insert (reply)

    ..but if the tabel has a insert trigger, than @@IDENTITY will return NULL (bug in sql7?)
    //LESZEK


    ------------
    Brian J. Smith at 5/4/99 12:58:17 PM

    If you're working with SPs, use output parameters.

    create procedure TestID(@ID int output)
    as
    insert into ident_tab
    values('Test&#39

    select @ID = @@Identity

    return
    GO

    ...Then in VB, when you bind to the SP, define the parameter as rdParamOutput, or adParamOutput (depending on whether or not you're using ADO or RDO.)


    ------------
    patrick beagan at 5/4/99 12:54:55 PM


    Can you do this programmitically in VB?

    ------------
    Brian J. Smith at 5/3/99 4:06:42 PM

    Check BOL and @@identity.


    ------------
    bigpow at 4/30/99 7:57:42 PM

    I have multiple processes inserting rows into a table. To ensure row uniqueness I have set a column "userID" as identity and the primary key. I always need to retrieve the newly created "userID" value immediately after the insert. What is the best way to get at this value if no other part of the row is unique?
    Immediately after the insert I can use SELECT max(userID) to get this, but what if a different process has inserted another record in the interim?
    Thanks in advance. -bigpow

Posting Permissions

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