Bulk Insert Statement

Hi All

I have a text file (sample below) i am bulk loading it into a staging table, the problem i am getting is the data is being loaded and scrambling the rows and I need the data to be imported exactly the same row by row

040207,"1007","","3319506/"
031207,"1509",">","US78016"
031207,"1509",">","AA004388"
031207,"1509",">","COMD88"
031207,"1509",">","US78016"
031207,"1509",">","AA001601"
031207,"1509",">","COMD88"
031207,"1510",">","US78016"
031207,"1510",">","AA004337"
031207,"1510",">","COMD88"
031307,"1138",">","US78016"
031307,"1139",">","AA004293"
031307,"1139",">","COMD81"

set nocount on
bulk insert data_load_stage.dbo.
from 'C:\load\CM07.txt'
with ( fieldterminator = ',')

missing a switch i think

thanks in advance

rich

Richie,

You'll need to add a clustered index to your staging table and then add the ORDER hint to your BULK INSERT statement (both matching the ordering of the input file).

From BULK INSERT in BOL:

ORDER ( { column [ ASC | DESC ] } [ ,... n ] )

Specifies how the data in the data file is sorted. Bulk load operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.

sql