SqlBulkCopy Order RRS feed

  • Question




    Does SqlBulkCopy insert rows in the same order that they appear in the data table?



    Tuesday, June 10, 2008 2:03 PM


  • Based upon my testing I don't see any guarantee that the order in the SQL Server destination table will be the same as the  source.
    Thursday, June 12, 2008 2:03 PM

All replies

  • Order is typically determined by a primary key. Do you have a primary key for the destination table?
    Tuesday, June 10, 2008 5:29 PM
  • In my scenario the table I'm filling doesn't have a primary key.  But I do want to make sure that the rows go in in the same order as they are in the data table (ie the 0th data row is the 1st added to the table, the 1st data row is the 2nd added to the table, and so forth).  Is this possible using SqlBulkCopy?

    Wednesday, June 11, 2008 5:44 PM
  • Based upon my testing I don't see any guarantee that the order in the SQL Server destination table will be the same as the  source.
    Thursday, June 12, 2008 2:03 PM
  • If I have a primary key in my table, is there any way to know what ids were created for my rows if I use SqlBulkCopy?  I thought that I could grab the latest and decrement it as I fill it into the local copy of my data -- but that won't work if the rows aren't inserted in order.

    Thursday, June 12, 2008 4:21 PM
  • I'm not exactly sure what you're asking. SQLBulkCopy doesn't create any IDs.

    BTW, is there a reason why the rows need to be in a certain order in the table? You can always sort the rows by a column when retrieving the data from the table.

    Thursday, June 12, 2008 5:47 PM
  • I'm using SqlBulkCopy to bulk a data table into a temporary table that mirrors my real table.  Then I select from the temporary table into my real table.  I've read that this is an efficient way to bulk data in and wanted to try it out.


    The problem is that I need the ids that are generated when the rows are inserted into the real table (to plug them back into my local copy of data).  If I could assume that everything ended up in the real table at the same exact time (ie other inserts don't happen during the insert from) and in the same order, then I could figure out the id values for every row.


    Thursday, June 12, 2008 6:20 PM
  • It sounds to me like you want to use a timestamp column so that you know when a row has been inserted or updated.

    SQLBulkCopy is somewhat limited in that it depends upon the contents of the source data. This can only be determined when you create and modify your source - before the bulk copy. SQLBulkCopy was not designed to manipulate the data during the copy process.

    Friday, June 13, 2008 12:28 PM

    Hmm, maybe SqlBulkCopy won't be able to do what I want. 


    Do you happen to know of any efficient way to insert data in bulk and reliably get the database generated id for each row that is bulked in?

    Friday, June 13, 2008 4:06 PM
  • Not if the ID is being generated upon insert, such as an autoincrement/identity column. If you're trying to mirror data between tables you may want to take a look at SQL Server Replication.
    Friday, June 13, 2008 4:26 PM
  • First of all I am not an SQL server expert but as far as I know:

    1) The order in wich rows are retreived from a SqlServer table via an "SELECT ..." is NOT GARANTEED if no "order by" statement is used. In other words
    "SELECT * FROM TableName" can give the records (rows) in a very different order every time it is called, regadless of the order in which those rows were inserted.

    2)An column(field) ot type primary key with autonumber may not generate consecutive numbers (ids) in a bulk insert. In the case of two "simultaneous" inserts requests on the same table , ids might not be  given consecutive for one of the two inserts.In other words, the insert "1" might get the ids 1;3;5;7 and the insert "2" might get the ids 2;4;6;8.

    I am having the same problem (retreive ids generated by an bulk insert) but so far I have only one idea:

    1)Add 2 fields to the destination & source table: 1 foreign key(GUID?), identical for all records(rows) in the current bulk, (value already set in source table) and 1 counter, set for each row in the source table(datatable , whatever) to the row's index.
    2)After insert, perform an SELECT on the same destination table to retreive the given ids, ORDERed BY the counter and filtered by the foreign key.
    And yes, it kind of cancels the advatages of using bulk insert, but...it's not my fault....
    Friday, May 15, 2009 10:57 AM