bcp and record order

Last Post 07 Mar 2008 07:44 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

07 Mar 2008 07:23 AM

If I have a flat file and bcp its records (lines) into a database table, does the bcp utility do the transfer of records at the same order as they appear in the flat file?

If my target table has an additional column that is an identity column, can I rely on the fact that the identity column will then correctly and accurately reflect the sequence of records as they resided in the flat file?


New Member
New Member

07 Mar 2008 07:44 AM
The answer is no, you cannot always rely on that. This is especially true the larger the file you have. What I've had to do in the past to preserve the line number order is to actually add line numbers to the flat file, using the findstr command:

findstr /N [a-z0-9] C:\original_file.txt > C:\new_file.txt

This is a bit of a hacky command line way to do it - I'm sure a quick app could do the job just as well. You might also have to change the findstr parameters based upon your data - this command just looks for any letters and numbers. But this adds line numbers to the flat file and then you can bcp the new flat file into a table, with an extra column for those line numbers.

Acceptable Use Policy