Search code examples
sqlsql-servert-sqlidentity-insert

batch insertion returns disorder identity fields

I have (tableA) with one identity field (ID), I need to insert multiple rows at once with one insertion operation, so I use table structure as a passing parameter to my stored procedure

I need the inserted id with the order of insertion so i'm running the following query in my SP (tableB has the same structure as tableA):

CREATE PROCEDURE ssp_Test
(
    tableA tableAType
)
AS
BEGIN

INSERT INTO tableB(field1, field2, ...)
OUTPUT INSERTED.ID
SELECT field1, field2, ... from tableA

END

If I run the above procedure with 100 records it returns the newly identity field with the order it inserted to the tableB, but when I run it with 500 records all of the inserted id returns with no reasonable order why this happens?

I don't need another temp table to put it there and sort that and return, i just want to know why its happening and is there any solution without using extra temp table for sorting ?

Solution

  • Look at this article: http://technet.microsoft.com/en-us/library/ms177564.aspx

    It states: "There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond."

    I suggest using a select after your insert to retrieve the ID's.