Why bcp import a file out of order in SQL 2008?

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  November 4, 2011 at 6:34 pm

  #1403554

  A lot of these problems can be resolved by importing into a staging table using BULK INSERT with OPTION (MAXDOP 1) set. You shouldn't import directly to a final table, anyway. Import to a staging table (usually a Temp Table) and do what should always be done... validate the data before sending it to its final resting place.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jared

  SSC Guru

  Points: 61793

  More actions

  November 4, 2011 at 8:57 pm

  #1403574

  Jeff Moden (11/4/2011)

  ---

  A lot of these problems can be resolved by importing into a staging table using BULK INSERT with OPTION (MAXDOP 1) set. You shouldn't import directly to a final table, anyway. Import to a staging table (usually a Temp Table) and do what should always be done... validate the data before sending it to its final resting place.

  With no response from the OP for quite awhile, I'm wondering if we really know what the problem is. My guess, at this point, is that he/she did a SELECT ... with no ORDER BY and assumed order of insertion. We still don't know how the OP determined that it was not inserting in the desired order 🙂

  Jared

  Jared
  CE - Microsoft

• ulisseslourenco

  SSC-Addicted

  Points: 417

  More actions

  November 16, 2011 at 8:15 am

  #1408583

  opc.three (11/4/2011)

  ---

  ulisseslourenco (11/1/2011)

  ---

  Hi guys

  There is an application in my company which imports files inside several tables on database. However when there are simultaneous files, a lot of them import registers out of order.

  i guess there is some configuration to avoid importing files wrong order in bcp.

  How import a file exactly the original file in bcp?

  best regards.

  You can do it by setting batch size to 1. Use the -b option for bcp.exe. Use the BATCHSIZE option for BULK INSERT. Here is a thread that describes the issue in detail:

  http://www.sqlservercentral.com/Forums/FindPost1127805.aspx

  Thanks dude,

  I will see that link.