Solved

Bulk Insert and Record Order

Posted on 2011-09-06
12
736 Views
Last Modified: 2012-06-27
We are reading a text file into a SQL table using Bulk Insert.  Will the records in the SQL table be in the same order as that of the text table?

Thanks,
Mark
0
Comment
Question by:MarkMahon
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 167 total points
ID: 36488516
It depends.  Is there a clustered index on the table that you are importing into?  That will determine the order.  If there isn't a clustered index, the order will be fairly random.  Why is the order of the table important?  When you read the data, you can order it with the ORDER BY clause.

Greg

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36488517
Normally, the row order is irrelevant.

But, if you are importing a table which is a parent table and you have other tables linking to this one, then you need to preserve the rows primary key.

I use code like this
 TRUNCATE TABLE TripleCDataConversion.dbo.Addresses
 BULK INSERT TripleCDataConversion.dbo.Addresses
 FROM 'D:\CCC2SQL\AD.TAB'
 WITH
  (
  FIELDTERMINATOR = '\t',
  KEEPIDENTITY,
  KEEPNULLS,
  LASTROW =               3313,
  ORDER (UniqueID ASC)
  )

Open in new window


The data has an identity column and I want to keep it when it reaches the SQL table.

Other than that, the order is irrelevant in my experience.
0
 
LVL 8

Assisted Solution

by:venk_r
venk_r earned 167 total points
ID: 36488519
If the table contains a clustered index ,yes it will put them in the same order.
0
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 36488571
>> Will the records in the SQL table be in the same order as that of the text table?
Yes.
0
 

Author Comment

by:MarkMahon
ID: 36488606
Jordedeoliveiraborges and Others -

I seem to be reading two answers that are at the opposite end of the spectrum ...

One (Jordedeoliveiraborges) says Yes but others say probably not as they will be stored in a random order.

Whos is correct?

Thanks,
Mark
0
 
LVL 40

Assisted Solution

by:RQuadling
RQuadling earned 166 total points
ID: 36488623
Depends.

If the table has a clustered index, then the data will be held in that order.

If the data in the text file is in the same order, then the data will be stored that way.

So, assuming 1 column of names and that the data is clustered on that column, the data will be held as

Adam
Brian
Charlie.

If the text file has the data as

Charlie
Brian
Adam

then the order will NOT be preserved.

So. The answer is ... it depends.

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 8

Expert Comment

by:venk_r
ID: 36488634
Look at the link
http://msdn.microsoft.com/en-us/library/ms188365.aspx

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )


Specifies how the data in the data file is sorted. Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any. If the data file is sorted in a different order, that is other than the order of a clustered index key or if there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid column names in the destination table. By default, the bulk insert operation assumes the data file is unordered. For optimized bulk import, SQL Server also validates that the imported data is sorted.

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36488640
In my experience, as soon as someone mentions "held in order" with regard to SQL, then something is missing from the question.

Specifically, why is the order important?

0
 

Author Comment

by:MarkMahon
ID: 36488948
I should've mentioed that there is no Clustered Index (yet).

The order is important in the application and because it is we may have to add an id column in the text file to maintain the order.  

But before we do that I am just trying to understand what the "default" behavior of SQL for this situation.  Given a text file being imported into SQL via Bulk Insert, and no clustered index is specified, will the order on the sql table be the same as the text file.  It sounds like the answer is NO.

Thanks,
Mark
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36488976
There is no "order" unless the data is clustered and/or indexed.

Add the order column to the data in the text file.

Use that column as a clustered index and primary key.

This will be the simplest way to preserve the order.
0
 

Author Comment

by:MarkMahon
ID: 36489112
Thank you to all very well done.

I actually "looked" ... opened the text files in Excel and compared to the SQL table resulting from the Bulk Insert... and yep the two files are NOT in the same order.

Thank you much, Mark
0
 

Author Closing Comment

by:MarkMahon
ID: 36489136
Nice job guys and gals
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now