November 16, 2009 at 6:35 am
Hi all,
I have a process, which needs to take in multiple files from a source folder, then update a few characters in the files. After the update, these files need to be output as a single file. All of this I have working, however once in a while, the output file is not in the same record order as the input files. I am at a loss on this one, as I can not manage to re-create the issue.
This process runs at least daily, and works for weeks on end, then for one run it does not order the output correctly.
So does anyone know of any particular gothca's with importing and exporting text files with regards to ordering?
I am operating under this assumption, which may not be right.
When SQL imports a data file, it does so in the order the records are in the file.
I import the files one at a time into a load table.
After each file is imported, I insert the new records into a working table with an identity column.
On the output I order the records by the identity column in order to output the records in the order they were imported.
My best thought here is adding an identity column to the load table, thinking that moving the data from load table to work table is altering the record order.
Does anyone know of any catches with ordering records like this? I am also puzzled why the issue would not occur more often if at any place in the process the records are being reordered. If it was entirely random, I would expect more frequent failures.
November 16, 2009 at 7:14 am
What are you using to do the import (DTS, SSIS, bcp, bulk insert)?
Do you have an indexes on the table?
By design RDBMS's do not guarantee any specific order when returning rows, so you need to explicitly use ORDER BY to get a specific sort order.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2009 at 7:22 am
Hi Jack, I am using bulk insert to import the files, and a DTS transform to output to a text file.
The only place I am not using an explicit sort is on the bulk insert step when initially loading the file. Is there a way to order a bulk insert?
November 16, 2009 at 7:32 am
According to the Bulk Insert entry in BOL there is an ORDER argument for Bulk Insert.
If you are explicitly doing an ORDER BY on your select the data should be returned in that order. Are you using proper datatypes? A sort on a date stored as varchar will be different than a sort on date stored as datetime.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2009 at 7:40 am
Thanks! I will have to test out the order by on the bulk insert. As far as the output sort, I am sorting on an int field (Double checked the data type).
I am still puzzled why it fails comparitively infrequently, and that I can not replicate the problem. I would tend to think if there was an issue in the TSQL code, it would occur more often than one in fifteen or so runs.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply