Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: How to get the @@Identity for multiple Inserts?

    Hi All,

    Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
    Since the insert is multiple iam not able to use the @@Identity.
    Can some one suggest me How can i over come this situtation.
    Also Triggers cant be used as the the records are of huge numbers.

    Eg:-
    INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users

    INSERT INTO UserDependent(UserID,OtherFields)
    VALUES(@@Identity,'SomeOtherValue')

    Thanks
    Tanveer
    Last edited by tanveer; 06-06-04 at 09:37.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    rewrite to a cursor insert
    or
    after the hughe insert, determine which are new and update/insert the depending table accordingly.
    I'm not entirely sure what you mean by 'this situation' though.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    A better solution would be to create a temporary table .. lets say UserConsolidated like
    (Userid,FirstName, SecondName,OtherFields) and then use this table to get data into User and UserDependent.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least for now, the @@identity values are processed as a block, in other words if you have five different inserts going (on different spids) at the same time, each block of the five will have a contiguous range of @@identity values. This means that if you insert 30 rows, the @@identity value will be for the first row, and @@identity+29 will be the value for the last row in your insert.

    Note that this will change at some point in time, probably in the release after Yukon.

    -PatP

Posting Permissions

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