0

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:

  1. create table #StagingTable(....) "
  2. BulkInsert DataTable into #StagingTable
  3. execute a reader on:

    insert into DestinationTable(....<columns_list>...)
    output inserted.ID -- primary key which is auto increment
    select <columns_list> from #StagingTable
    
  4. then in C# I read all the generated ids in a list. The read order is not correctly mapped to DataTable.Rows order.

My question is: If I sort this list of ids ascendingly without doing any mappings (i.e. c# simple: list.Sort()) - will this be the correct inserted order so that table.Rows[k] corresponds to id = list[k] ?

Thank you!

/Edit:

To give you the full context, I have a method like this in C#

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 => !table.Columns[i].AutoIncrement).
            Select(i => "[" + 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 => !table.Columns[i].AutoIncrement).
            Select(i => "[" + 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<long> list = new List<long>();
                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();
        }
    }
}

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.

RobertB.
  • 51
  • 3
  • This has been answered here http://stackoverflow.com/questions/35687950/combine-output-inserted-id-with-value-from-selected-row – Jon Feb 01 '17 at 15:26
  • That answer solves the problem using the merge command and ids mapping in C#. I send a row ID and receive that row ID with the associated inserted ID. Isn't this solution with sorting more simple and should it work with sql 2005 without the merge command? – RobertB. Feb 01 '17 at 21:50
  • @RobertB. what are you trying to do? There is no order guarantee even in a *single* statement unless you have an `ORDER BY`, much less across unrelated statements. You could sort your DataTable and add an `ORDER BY` to INSERT – Panagiotis Kanavos Feb 03 '17 at 09:36
  • What is the *actual* problem you want to solve? Why do you care about the generated IDs? Why not generate IDs on the client, reload the data or simply perform any bulk processing on the server? Are you trying to handle DataTables with relations? Improve the performance of an ORM? – Panagiotis Kanavos Feb 03 '17 at 09:38
  • I am trying to handle bulk inserts of data with relations, that happen in parallel, multiple users/computer at a time. I need the solution to be as efficient as possible. I only need the ids to come back from the server in an order I can determine on the client - preferably the original insert order. – RobertB. Feb 04 '17 at 02:37

0 Answers0