title: Bulk insert generated ids return order
body: <p>I want to very efficiently execute a bulk insert of a DataTable to SQLServer from a C# application and retrieve the inserted ids in the right order.
  In order to get the inserted primary keys auto incremented values I use the following approach on the same connection:</p>
  
  <ol>
  <li>create table #StagingTable(....) "</li>
  <li>BulkInsert DataTable into #StagingTable</li>
  <li><p>execute a reader on: </p>
  
  <pre><code>insert into DestinationTable(....&lt;columns_list&gt;...)
  output inserted.ID -- primary key which is auto increment
  select &lt;columns_list&gt; from #StagingTable
  </code></pre></li>
  <li><p>then in C# I read all the generated ids in a list. The read order is not correctly mapped to DataTable.Rows order. </p></li>
  </ol>
  
  <p>My question is: 
      If I sort this list of ids ascendingly without doing any mappings (i.e. c# simple: <code>list.Sort()</code>) - will this be the correct inserted order so that <code>table.Rows[k]</code> corresponds to <code>id = list[k]</code> ?</p>
  
  <p>Thank you!</p>
  
  <p></p>
  
  <p>/Edit:</p>
  
  <p>To give you the full context, I have a method like this in C#</p>
  
  <pre><code>using System.Data;
  using System.Data.SqlClient;
  using System.Web.UI.WebControls;
  
  public static class SQLWrite
  {
      public static void BulkWrite(this DataTable table, string destinationTable, SqlConnection connection, int batchSize)
      {
          if (connection.State != ConnectionState.Open)
          {
              connection.Open();
          }
          string columnNamesAndSQLType = string.Join(", ", Enumerable.Range(0, table.Columns.Count).
              Where(i =&gt; !table.Columns[i].AutoIncrement).
              Select(i =&gt; "[" + table.Columns[i].ColumnName + "] " +
                  Parameter.ConvertTypeCodeToDbType(Type.GetTypeCode(table.Columns[i].DataType)).ToString()));
          string columnNames = string.Join(", ", Enumerable.Range(0, table.Columns.Count).
              Where(i =&gt; !table.Columns[i].AutoIncrement).
              Select(i =&gt; "[" + table.Columns[i].ColumnName + "]"));
          using (var command = connection.CreateCommand())
          {
              command.CommandText = @"
  CREATE  TABLE #StagingTable(
      RowNumber INT IDENTITY(1, 1) NOT NULL, 
      " + columnNamesAndSQLType + @")     ";
              command.ExecuteNonQuery();
          }
          DataColumn autoIncrementColumn = null;
          using (var copy = new SqlBulkCopy(connection))
          {
              copy.BatchSize = batchSize;
              copy.DestinationTableName = destinationTable;
              foreach (DataColumn column in table.Columns)
              {
                  if (column.AutoIncrement)
                  {
                      autoIncrementColumn = column;
                      continue;
                  }
                  copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
              }
              copy.WriteToServer(table);
          }
          using (var command = connection.CreateCommand())
          {
              command.CommandText = @"
  INSERT  INTO dbo.DestinationTable(" + columnNames + @")
  OUTPUT  INSERTED.ID 
  SELECT  " + columnNames + @"
  FROM    #StagingTable 
  ORDER   BY RowNumber    
  ";   //Will the inserted ID values be retrieved in the natural order of insertion - by RowNumber ?
              using (var reader = command.ExecuteReader())
              {
                  List&lt;long&gt; list = new List&lt;long&gt;();
                  while (reader.Read())
                  {
                      list.Add(reader.GetInt64(0));
                  }
                  list.Sort();
                  int rowIndex = 0;
                  foreach (DataRow row in table.Rows)
                  {
                      row[autoIncrementColumn] = list[rowIndex];
                      rowIndex++;
                  }
              }
          }
          if (connection.State != ConnectionState.Closed)
          {
              connection.Close();
          }
      }
  }
  </code></pre>
  
  <p>Assume each table has a bigint primary key auto-increment column called ID.
  I want to know if the above code retrieves the SQL generated IDS and associates them with the data table correctly.</p>