Problem importing sequential text files

Question

Problem importing sequential text files

Anubis666

SSC Rookie

Points: 39
More actions
April 10, 2002 at 2:29 am

#151681

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.

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

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

svenhammesfahr SSC Rookie Points: 39 More actions · Answer 1

Hi Bart,

SQL does not gurantee that records from a table are returned in a specific order unless you use the order by. As far as I can see your data does not have a column that you could use to order the result.

I'd suggest to add an identity column to your table. Then you can just order by this column and you'll see that the records are inserted in the correct order.

Hope this helps

sven

Anubis666 SSC Rookie Points: 39 More actions · Answer 2

Thanks Sven!

Well your answer is what I feared and what I thought myself.

But my collegues don't believe this at all. Is there a place where I can verify this on the Microsoft Site?

Thanks again,

Bart.

Anubis666 SSC Rookie Points: 39 More actions · Answer 3

I've verified some things and Microsoft says that when you use a bulk copy, it's indeed not garanteed that all the records will be inserted sequentially.

For certain files I used bulk copy and for others I used ActiveX copy (with VBscript) to import the textfile data.

I'm now running some test productions using only ActiveX copy. Apparently the problem seems to be solved... But I still have to run a number of productions. The error didn't occur everytime...

Greetz,

Bart.