March 10, 2012 at 5:26 am
I am using BCP out to extract some 50 records into excel. In table, the records are sorted correctly (ie by datetime on which record is inserted). But when I extract to excel, the order goes haywire.
Any ideas to correct the order while extracting?
How does bcp works in such a case?
In the SQL Server itself, i extract the file as .xls. It does not have Excel installed. And, this out-of-order happens randomly.
NOTE: Moved this topic to SQL 2008 forum
March 13, 2012 at 3:11 pm
balasach82 (3/10/2012)
I am using BCP out to extract some 50 records into excel. In table, the records are sorted correctly (ie by datetime on which record is inserted). But when I extract to excel, the order goes haywire.Any ideas to correct the order while extracting?
How does bcp works in such a case?
In the SQL Server itself, i extract the file as .xls. It does not have Excel installed. And, this out-of-order happens randomly.
NOTE: Moved this topic to SQL 2008 forum
There is no guaranteed sort-order in SQL Server unless you provide an ORDER BY-clause with your retrieval request. In the case of BCP OUT you do not specify an ORDER BY so SQL Server will deliver the data to you "in the fastest way it possibly can" which means no ordering is applied.
What you are probably seeing when you say "In table, the records are sorted correctly" the data is returned to your query tool (e.g. SSMS) in the same order of the clustered index, however that is a coincidence and is not guaranteed. Here is an article that explains the issue, with a demo that dispels the myth that "data with a clustered index is always returned in the clustered index order, even when not providing a clustered index".
ORDER BY Clause with clustered index[/url]
So, to answer your original question, if you change your BCP command to use QUERYOUT and issue it as "SELECT * FROM dbo.your_table_name ORDER BY your_column_name" then you will get results in the order you expect.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply