Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does bcp out maintain row order while exporting into a data file?

I need to export data to a file from a huge table with only one column but thousands of rows where the ordering of row is important. I am using bcp command as below
bcp DBNAME.dbo.tblName out mydata.dat -Uusername -Ppassword -c
I checked with the table having 10 rows and I see that the order of the rows is maintained in the data file. But can I assume bcp would maintain the order if the number of rows is say more than 10000?

like image 243
Thunderhashy Avatar asked Jan 06 '10 20:01

Thunderhashy


2 Answers

I think it will if you use a sql statement (with ORDER BY) in your bcp command:

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

where they have the following example:

SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors 
   ORDER BY au_lname" queryout "' 
like image 56
davek Avatar answered Dec 31 '22 20:12

davek


You should never assume that a SQL SELECT (or bcp) will return values in a certain order or the same order unless you use an ORDER BY clause.

Generally, the values are returned in order based on an index on the table.

However, I have seen cases where the values are not returned in index order. It appears that rows in the cache may be returned before rows from disk even if the rows on disk are the first in the index.

like image 24
Darryl Peterson Avatar answered Dec 31 '22 19:12

Darryl Peterson