Question

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?

Answer 1

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 "

Answer 2

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.

友情链接