Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Location
    Belgium
    Posts
    3

    Unanswered: Problem importing sequential text files

    Hello everybody

    I have a rather strange problem I guess. Most likely I did something wrong but I can't find it right now. So this is what happens.

    Well, I receive a number of text files and I have to import them into SQL server 7. I use Vbscript (ActiveX import) to modify some data before putting it all into my tables. Sql server imports all records perfectly but sometimes the sequential order of the text file is not respected when I look into the table. I'd like to explain this a bit better.

    Let's say I have 3 types of records in the textfile. "NATIONAL" , "DISTRICT" and "AREA". The textfiles (fixed columns) look a bit like this.

    0123321334253 NATIONAL 432748234
    2347234712357 DISTRICT 234852348
    2348235724137 DISTRICT 242752348
    6975067587684 DISTRICT 664568483
    6534534537453 AREA 324537453
    6578305607965 AREA 485834690
    3247573284920 AREA 453753648
    6795673435734 AREA 457352888
    4564573486943 AREA 583475386

    So the order of the records is really important. Mostly SQL Server imports all records correctly but sometimes (without changing the inputfiles nor the DTS package !!!!) it mixes up the order of the records. After importation into a table it could look like this.

    3247573284920 AREA 453753648
    6795673435734 AREA 457352888
    0123321334253 NATIONAL 432748234
    2347234712357 DISTRICT 234852348
    6534534537453 AREA 324537453
    6578305607965 AREA 485834690
    4564573486943 AREA 583475386
    2348235724137 DISTRICT 242752348
    6975067587684 DISTRICT 664568483

    How is that possible? Most likely it's a setting I configured wrongly but what worries me is that mostly everything goes right and without changing anything it could be mixed up.

    Can you please help me?

    Anyway, thanks a lot!

    Greetz,

    Bart.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    SQL Server will maintain the record order as inserted UNLESS you apply a clustered index. I would check for indexes the next time this happens. Can anyone esle in your organization change the schema?

    Also, do you use only one process to read the input files? Two process could also produce a mixed order.

    Could you add an identity column to preserve the inserted order?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Location
    Belgium
    Posts
    3
    Hello Paul,

    First of all a big thank you for your reply.

    Well, let me answer your questions first

    I don't use clustered indexes and I don't use multiple read processes. So I only import one text file at a time.

    I have also tried to add an identity field to a record. In my ActiveX import script (vbscript) I test on the used level. If it's "NATIONAL" I put "1" into the ID column, when it's "RESEAU" I put "2" into the ID column and so on.

    So I import everything into a temporary table and afterwards I put everything with an "ORDER BY ID ASC" into the final table.

    You won't believe it, but even after that "ORDER BY" I SOMETIMES, not always, have a wrong order into the final table. So it's possible that in the final table I have such an ID column

    1
    2
    2
    2
    2
    3
    3
    3
    3
    3
    4
    4
    3
    3
    3
    5
    5
    5

    You can surely understand that this irritates me a lot.

    How is that possible?

    Greetz,

    Bart.

  4. #4
    Join Date
    Apr 2002
    Posts
    5
    By definition record order is unimortant in a relational structure. SQL Server does not guarantee row order because internally it stores the data in pages that may or may not be sequential.

    Why not just apply the 'ORDER BY' clause when you retrieve the records from the database -- that way it makes no difference which order they are stored. An index on that field would speed this operation up a great deal.

    BTW, IDENTITY fields are auto-incrementing -- you should not be trying to insert discrete values.

    Regards,
    Jason Woosley
    SQL Server DBA

  5. #5
    Join Date
    Apr 2002
    Location
    Belgium
    Posts
    3
    Thanks for your reply Jason.

    Your last solution is exactly what I tried last week. And indeed, this works perfectly. I only forgot to post this answer onto the messageboard.

    Greetz,

    Bart.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •