SqlBulkCopy is not inserting rows in same order fr

Question

objDataTable=ConversionClass.ConvertToDataTable(strFilePath,"tbl_transactonData", ",");       String strConnectionString =ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

        SqlBulkCopy bulkCopy = new SqlBulkCopy(strConnectionString);
        bulkCopy.DestinationTableName = "tbl_AllTransactiondata";
        try
        {
            bulkCopy.WriteToServer(objDataTable);
        }
        catch (Exception ex)
        {

        }
        finally
        {
            bulkCopy.Close();
        }

I need to transfer value from datatable to Sql Table using SqlBulkCopy. But SqlBulkCopy is not inserting rows in proper order .Please suggest solution for the same.

Answer 1

You could use the order by clause in sql. Of course this assumes that your data is already sorted by one column. https://msdn.microsoft.com/en-us/library/ms188385.aspx?f=255&MSPPError=-2147217396

You could create a new table that is a sorted version of the old table after you have completed all of the inserts. Again, same assumption. How to sort values in columns and update table?

You could insert one row at a time with a foreach loop. You would also need to create a DataRow[] of one row each time. https://msdn.microsoft.com/en-us/library/ac44f8yy(v=vs.110).aspx

The ideal solution in my mind is either using the order by clause or sorting it after all of the insertions. Unfortunately, I couldn't find any way to guarantee it would insert rows sequentially.

Class description: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

Answer 2

Typically there is no guaranteed order for bulk insert operations. Besides inserting each row individually (neither fun nor efficient), you might could try using the TableLock SqlBulkCopyOption.

new SqlBulkCopy(strConnectionString, SqlBulkCopyOptions.TableLock);

It will attempt a minimally-logged operation which can only be done via an ordered insert, which will be done via a background sort in tempdb first.