SQL Server Performance Forum – Threads Archive
bcp file imported out of order
When I run the following statement: bcp "DCCM.dbo.filetest" in "i:3950463.edi" /c /S"MYSERVER" /U"sa" /P"password" /t"~" /r"~" The file I:3950463.edi (or any file) imports into SQL Server table successfully, but the rows are all out of order. Any ideas?Thanks-
Matt
Would you by any chance be having any index on the ‘filetest’ table? Nathan H.O.
Moderator
SQL-Server-Performance.com
By default, unless you have a clustered index, data order is of no relevance to sql. If you need to retrieve your data in order after its imported, you need to either sort it during the selection, or put a clustered index on the ‘sort’ field. The power of SQL is in its ability to do set based retrievals, updates etc. Because of this, unless you specify otherwise, sql doesnt care about order.
Just curious if it’s possible to use T-SQL to import a text file in the same order in which it was created? Or do I need to use another tool for this? I’ve managed to use the BCP utility to import a data file in order several hundred times until one field was changed in the file. Now it’s off by one record.
Thanks.
BCP will import data in the order it occurs in the input file. If there is a clustered index on the table, SQL will resort the rows as needed. If the datafile is already sorted you can used the ORDERED argument in BCP to tell SQL it does not have to resort. If there is no clustered index, the table will be physically ordered just as it was loaded but the only reliable way to retrieve it back again in the same order would be to create an identity column on the table and order by that for retrieval. To do that you would need to create a format file to tell BCP it is not loading data into the identity column, or create a view of the table that does not include the identity column and BCP into the view.
]]>