Inserting multiple rows into MS SQL Server and retrieve all the new table ID's back

Question

Looking at the example given here: https://stackoverflow.com/a/452934

I understand that I will need to iterate through a loop and append value clauses, but what I am missing is how to amend the query to return all the ID's for the newly created records and retrieve them in C#?

For Example my current code can be seen below, I would like to change it to insert multiple rows in one query and retrieve the newly created Id's as a List of integers ideally.

in_new_id = -1;
String query = "INSERT INTO " +   DB_Base.DBTable_Customer_Order_Table + "(" + DB_Base.DBTable_Customer_Order_Table_Customer_ID + "," + DB_Base.DBTable_Customer_Order_Table_ProductId+")";
query += " OUTPUT INSERTED." + DB_Base.DBTable_Customer_Order_Table_ID;
query += " VALUES ( @customerId, @productId);";

using (SqlConnection conn = new SqlConnection(GeneralConfig.DB_STR()))
{
    SqlCommand sql_command = new SqlCommand(query, conn);
    sql_command.Parameters.AddWithValue("@customerId", data_obj.customerId);
    sql_command.Parameters.AddWithValue("@productId", data_obj.productId);
    if (!String.IsNullOrEmpty(query) && sql_command != null && conn != null)
    {
         sql_command.Connection.Open();
         if (sql_command.Connection.State == System.Data.ConnectionState.Open)
         {
             object out_new_id = sql_command.ExecuteScalar();
             if (out_new_id != null)
             {
                 in_new_id = (int)out_new_id;
             }
             sql_command.Connection.Close();
             return ENUM_DB_Status.DB_SUCCESS;
         }
         else
         {
             in_new_id = -1;
             return ENUM_DB_Status.DB_CONNECTION_COULD_NOT_OPEN;
         }
    }
}

return ENUM_DB_Status.DB_FAIL;

Use Output Clause to get multiple IDS into another table. Then retrieve all data from the new table. This could be done in a batch without looping — ashim, Jul 21 '15 at 07:32
Be aware that a list of integers may be easy to retrieve but there is no guarantee (that I'm aware of) that e.g. the first value in the list would correspond to the first row in the `VALUES` clause, second with second, etc. — Damien_The_Unbeliever, Jul 21 '15 at 07:33
@AshimDas do you have an example of how to do this, I cant find an example for multiple row inserts only for single insert -> output id. — Dizzle, Jul 21 '15 at 07:56

Zippy · Accepted Answer · 2015-07-21T08:29:35.610

2

Use this:

List<int> ids = new List<int>();
using (SqlCommand command = new SqlCommand(@"declare @T TABLE(Id int)
                                                                INSERT INTO YourTableName(YourTableColumnNames)
                                                                OUTPUT Inserted.Id into @T VALUES 
                                                                (YourValues1),
                                                                (YourValues2),
                                                                (YourValues3),
                                                                (etc...) select Id from @T ", con))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = int.Parse(reader[0].ToString());
                                ids.Add(id);
                            }
                        }
                    }

Warning!!! This will work only if you're using SQLServer 2008 R2 or higher.
Edit: As Damien said in the comments : "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."

edited Jul 21 '15 at 08:29

answered Jul 21 '15 at 08:07

Zippy

1,804
5
27
36

Even **if** the order of rows returned by the output clause is guaranteed (I'm not sure it is, as indicated in a comment to the question), *any* ordering guarantee has been lost by your final `select ID from @T` query since it doesn't specify an `ORDER BY` clause. – Damien_The_Unbeliever Jul 21 '15 at 08:21
@Damien_The_Unbeliever Agreed. From what i've tested (tried to insert 5 rows into a table) the ids returned where in order (might be a coincidence tho') – Zippy Jul 21 '15 at 08:23
2

In fact, we even have [OUTPUT clause](https://msdn.microsoft.com/en-us/library/ms177564.aspx): "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." – Damien_The_Unbeliever Jul 21 '15 at 08:26
@Damien_The_Unbeliever Thanks! Updated the answer with your info. – Zippy Jul 21 '15 at 08:29
you can output multiple columns to make each result unique enough, that you can match the correct ID to the correct item from the insert. declare @T TABLE(Id int, [anothercolumn] nvarchar(25)).... OUTPUT Inserted.Id, Inserted.[anothercolumn] into @T VALUES..... select Id, [anothercolumn] from @T – Heriberto Lugo Oct 01 '20 at 22:27

Inserting multiple rows into MS SQL Server and retrieve all the new table ID's back

1 Answers1