Post reply

imported data is reordered

  • July 23, 2004 at 4:14 pm

    #109257

    I am loading around 1 million lines of data from a text file using BULK INSERT.  After it loads the data, I am finding that the records are not in the same order as the text file that I loaded. 

    Why is this happening and how can I stop it?

  • July 24, 2004 at 10:26 pm

    #515989

    Hi,

    Just curious, Why do you care what order the rows are in? when you are querying the data you can sort them with the order by clause.

    tal mcmahon

    Kindest Regards,

    Tal Mcmahon

  • July 25, 2004 at 7:29 am

    #515995

    Good question!

    The input file is medical claims data that is not in a columnar format (neither fixed nor delimited).  It is record driven with each record having a record identifier (1st 3 characters).  This format is called the National Standard Format (NSF) for electronic transmission.   One medical claim may be 15 or 20 records.  I am loading the data file into Sequel Server into one data field (320 characters wide) and then running a TSQL script with a cursor to loop through the 15-20 records and flatten out the claim to one record so we can analyze the data.

    Any ideas why it doesn't keep the order?  I was using bulk insert without a format file since I only had one data field.

    Since posting my question I was able to solve the problem by creating a IDENTITY column in the destination table and by using a format file with BULK INSERT (needed since I now have two data fields, sequence number and claims data).  As each record is loaded, the IDENTITY column is recording a sequence number for each record.  When I run the cursor, I sort by the sequence field. 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply