Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: INSERT @@identity at once

    Hi

    how can I insert @@identity at once (for MS SQL 2000 / 2500)

    INSERT INTO Table1 (name) VALUES ('any')

    DECLARE @ID int SELECT @@Identity

    INSERT INTO Table2 (id_Table1) VALUES (@ID)


    does not work

    thank you

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    DECLARE @ID int

    INSERT INTO Table1 (name)
    VALUES ('any')

    SELECT @ID = @@Identity

    INSERT INTO Table2 (id_Table1)
    VALUES (@ID)
    Regards
    Kris Zywczyk

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but consider using SCOPE_IDENTITY instead of @@IDENTITY.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    it works with @@IDENTITY

    I am getting an error with

    DECLARE @ID int

    INSERT INTO Table1 (name)
    VALUES ('any')

    SELECT @ID = SCOPE_IDENTITY

    INSERT INTO Table2 (id_Table1)
    VALUES (@ID)



    how do you use it ?


    thank you

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    eventually the code with TRANSACTION will be great

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    There's examples in the MSDN.

    SCOPE_IDENTITY() is a function. If you read up op it, you'll see there's some subtle differences (with @@Identity) that apply if you're using sub-procedures or triggers, ie: the scope is different.
    Last edited by vich; 11-10-06 at 17:32.

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    yes but I get : The name "SCOPE_IDENTITY" is not permitted in this context. Valid expressions are constants, constant expressions


    INSERT INTO Table1 (name)
    VALUES ('any')

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

    INSERT INTO Table2 (id_Table1)
    VALUES ([SCOPE_IDENTITY])

    does not work at all

  8. #8
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by anselme
    yes but I get : The name "SCOPE_IDENTITY" is not permitted in this context. Valid expressions are constants, constant expressions


    INSERT INTO Table1 (name)
    VALUES ('any')

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

    INSERT INTO Table2 (id_Table1)
    VALUES ([SCOPE_IDENTITY])

    does not work at all
    SCOPE_IDENTITY() is a function. As such, it returns a value. Your code is not placing that value into a declared field.

    Code:
    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
    will simply return the value to your standard output device with the label of "SCOPE_IDENTITY". I think you want to declare a @SCOPE_IDENTITY variable field.

    Try:

    Code:
    DECLARE @SCOPE_IDENTITY
    INSERT INTO Table1 (name)
    VALUES ('any')
    
    SET @SCOPE_IDENTITY = SCOPE_IDENTITY()
    
    INSERT INTO Table2 (id_Table1)
    VALUES (@SCOPE_IDENTITY)
    OR, if you know there's no triggers on your tables, simply:
    Code:
    INSERT INTO Table1 (name)
    VALUES ('any')
    
    INSERT INTO Table2 (id_Table1)
    VALUES (SCOPE_IDENTITY())
    (truthfully; I'd want to test the trigger scenerio. Using a DECLARE is safer, but may be unnecessary at the end of the day. I'm not as comfortable with trigger scopes as the experts floating around here)
    Last edited by vich; 11-11-06 at 00:35.

  9. #9
    Join Date
    Dec 2005
    Posts
    266
    now it works

    thank you

Posting Permissions

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