Can I insert with a table parameter and also get identification values?

Question

I am trying to insert records using the high-performance table parameter method ( http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/ ) and I am very interested if it is possible to return values identities for each record that I insert.

At the moment, the answer seems no - I insert the data, and then return the identification values, and they do not match. In particular, they do not correspond approximately 75% of the time, and they do not correspond in an unpredictable way. Here is the code that replicates this problem:

// Create a datatable with 100k rows
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("item_id", typeof(int)));
dt.Columns.Add(new DataColumn("comment", typeof(string)));
for (int i = 0; i < 100000; i++) {
    dt.Rows.Add(new object[] { 0, i.ToString() });
}

// Insert these records and retrieve back the identity
using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=testdb;Integrated Security=True")) {
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("proc_bulk_insert_test", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;

        // Adding a "structured" parameter allows you to insert tons of data with low overhead
        SqlParameter param = new SqlParameter("@mytable", SqlDbType.Structured);
        param.Value = dt;
        cmd.Parameters.Add(param);
        SqlDataReader dr = cmd.ExecuteReader();

        // Set all the records' identity values
        int i = 0;
        while (dr.Read()) {
            dt.Rows[i].ItemArray = new object[] { dr.GetInt32(0), dt.Rows[i].ItemArray[1] };
            i++;
        }
        dr.Close();
    }

    // Do all the records' ID numbers match what I received back from the database?
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM bulk_insert_test WHERE item_id >= @base_identity ORDER BY item_id ASC", conn)) {
        cmd.Parameters.AddWithValue("@base_identity", (int)dt.Rows[0].ItemArray[0]);
        SqlDataReader dr = cmd.ExecuteReader();
        DataTable dtresult = new DataTable();
        dtresult.Load(dr);
    }
}

The database is defined using this SQL server script:

CREATE TABLE bulk_insert_test (
    item_id int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    comment varchar(20)
)
GO

CREATE TYPE bulk_insert_table_type AS TABLE ( item_id int, comment varchar(20) )
GO

CREATE PROCEDURE proc_bulk_insert_test
    @mytable bulk_insert_table_type READONLY
AS

DECLARE @TableOfIdentities TABLE (IdentValue INT)

INSERT INTO bulk_insert_test (comment)
OUTPUT Inserted.item_id INTO @TableOfIdentities(IdentValue)
SELECT comment FROM @mytable

SELECT * FROM @TableOfIdentities

: , proc_bulk_insert_test, . item_id, OUTPUT.

, SELECT , , , , , SQL . - , ?

: . , , # , SQL Server , . ; #, , # .

, , . , :

• , , 15 , 15 , . , .
• , . # . varchar decimal, , , .
• , , , SQL Server . scope_identity() OUTPUT, .

, , SQL Server , . SQL- , ?

EDIT2: , Cade Roux :

http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts

"ProductNumber" "output" . , , .

Answer 1

TVP , . , . , SELECT * ORDER BY. ? SQL Server, . , . , , :

DECLARE @TableOfIdentities TABLE (IdentValue INT, comment varchar(20))

INSERT INTO bulk_insert_test (comment)
OUTPUT Inserted.item_id, Inserted.comment 
INTO @TableOfIdentities(IdentValue, comment)
SELECT comment FROM @mytable

SELECT * FROM @TableOfIdentities

( #):

DECLARE @t bulk_insert_table_type;
INSERT @t VALUES(5,'foo'),(2,'bar'),(3,'zzz');
SELECT * FROM @t;

EXEC dbo.proc_bulk_insert_test @t;

:

1   foo
2   bar
3   zzz

, ( "", TVP), ORDER BY item_id .

, , , :

• TVP , . , , IMHO.

• TVP, , ORDER BY . , , parallelism, MAXDOP 1.

, , ORDER. ? - , , IDENTITY.

Answer 2

no ORDER BY : SELECT * FROM @TableOfIdentities, . , , , INNER JOIN , ORDER BY, .