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:
- create table #StagingTable(....) "
- BulkInsert DataTable into #StagingTable
execute a reader on:
insert into DestinationTable(....<columns_list>...) output inserted.ID -- primary key which is auto increment select <columns_list> from #StagingTable
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.