Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Stored Proc insert - multiple tables

    Hi,
    Any suggestion on how to insert data into 2 tables in a stored proc.
    Scenario is:

    Table 1 insert is generated and the primary key is created. This key is an identity column and is the only thing that makes the row unique.

    Table 2 needs an insert but one of the columns that is needed is the newly created column 1 primary key.

    How do I know what the new rows primary key value is from Table 1?


    Thanks in advance,
    Rebecca

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Use the SCOPE_IDENTITY() function.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That won't work unless she is only inserted one record at a time.

    Rebecca, your data should have a "natural" key in addition to the "surrogate" key you are creating. The natural key is a column of combination of columns inherent in the data which uniquely identify each record. You can use the natural key to look up the newly created surrogate keys in your first table.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If there are multiple inserts (it's not really specified here), she can see if the OUTPUT clause of the INSERT statement will work for her. That may only work if Table1 has a natural or other unique key that she can map the identity column to.

Posting Permissions

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