Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: [RESOLVED] Insert question - PK and FK relationship

    Given table A and table B where table A has a fk to table Bs primary key.

    I insert a row into table B and want to know its id so when I insert a row into table A Ill have the value for the relationship of the two rows.

    How do I get the id of the new row I just inserted into table B?

    Thanks.
    Last edited by M Mock; 02-19-08 at 20:49. Reason: Resolved

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi there

    Assuming this ID field is an identity integer then the code would be

    DECLARE @ID INT
    INSERT INTO table()
    select blah blah

    SET @ID = SCOPE_INDENTITY()

    PRINT @ID --FOR SHOWING THE KEY
    Last edited by SQLSlammer; 02-19-08 at 12:22.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    I'm researching @@IDENTITY because that was in the back of my mind as being what I wanted...

  4. #4
    Join Date
    Jul 2006
    Posts
    111
    Oh, thank you SQLSlammer, that was quick! I should've refreshed before posting back to myself!

  5. #5
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    USE SCOPE_INDENTITY(), because it will be the ID for your scope (your transaction), otherwise there is a possibility you could pick up the wrong ID if someone else is inserting at the same time.

  6. #6
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    hehe no probs

    I edited my first post btw, missed the word SET

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SQLSlammer
    I edited my first post btw
    you may want to edit it again, as there is no such function as SCOPE_INDENTITY()

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by SQLSlammer
    USE SCOPE_INDENTITY(), because it will be the ID for your scope (your transaction), otherwise there is a possibility you could pick up the wrong ID if someone else is inserting at the same time.
    Actually both are limited in scope to the session. It is that @@INDENTITY will pick up the last identity value generated by the statement irrespective of whether or not it was generated by the target object. In other words, if a trigger fires and inserts into other tables @@IDENTITY will return the last generated identity value, SCOPE_IDENTITY() will get the one from the table with the trigger.

    Ya might be thinking of IDENT_CURRENT

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    you may want to edit it again, as there is no such function as SCOPE_INDENTITY()

    Thank the lord no one showed you this post then
    http://www.dbforums.com/showpost.php...93&postcount=9


    .......................
    oops
    Last edited by pootle flump; 02-19-08 at 13:04. Reason: made the edit before Mr Pedantic saw the error...

  10. #10
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Quote Originally Posted by r937
    you may want to edit it again, as there is no such function as SCOPE_INDENTITY()

    lol ok I shouldn't type so fast

  11. #11
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Quote Originally Posted by pootle flump
    Actually both are limited in scope to the session. It is that @@INDENTITY will pick up the last identity value generated by the statement irrespective of whether or not it was generated by the target object. In other words, if a trigger fires and inserts into other tables @@IDENTITY will return the last generated identity value, SCOPE_IDENTITY() will get the one from the table with the trigger.

    Ya might be thinking of IDENT_CURRENT
    Ok I just read that in BOL, I never knew that about triggers and SCOPE_IDENTITY(), thanks

    To the orginal poster, both scope_identity() and @@IDENTITY will work but I think as Pootle says its better to use @@IDENTITY

    See Triggers screw everything up!! :P
    Last edited by SQLSlammer; 02-19-08 at 12:57.

  12. #12
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Actaully I remember now why I always use scope_identity(), I saw a problem a few years back when read uncommitted was set for the isolation mode that @@identity was returning incorrect ID's, this may well have been fixed in service pack 3a/4.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by SQLSlammer
    Ok I just read that in BOL, I never knew that about triggers and SCOPE_IDENTITY(), thanks

    To the orginal poster, both scope_identity() and @@IDENTITY will work but I think as Pootle says its better to use @@IDENTITY
    No no no - you had the right answer, wrong reasoning. Defo go with SCOPE_IDENTITY()

    Quote Originally Posted by SQLSlammer
    See Triggers screw everything up!! :P
    See not understanding how triggers interact with other processes screws everthing up!! :P

  14. #14
    Join Date
    Jul 2006
    Posts
    111
    I definitely went with SCOPE_IDENTITY!

    Thanks,
    Marlene

  15. #15
    Join Date
    Aug 2008
    Posts
    4
    I receive:
    Msg 195, Level 15, State 10, Line 8
    'SCOPE_INDENTITY' is not a recognized built-in function name.

Posting Permissions

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