MarkMahon
asked on
Bulk Insert and Record Order
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
Thanks,
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> Will the records in the SQL table be in the same order as that of the text table?
Yes.
Yes.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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?
Specifically, why is the order important?
ASKER
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
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
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.
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.
ASKER
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
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
ASKER
Nice job guys and gals
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
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.