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.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?|||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

:mad: You can surely understand that this irritates me a lot.

How is that possible?

Greetz, :confused:

Bart.|||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|||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.sql