BCP

[DriesM]

Hello,

I us bcp 8.00 in a few vbs scripts to import a txt data file into an sql table.
After running one specific script the records are stored mixed up in the sql table.
The table is single column only containing one char field.
How does one control the record order by useing bcp?
Other scripts, doing almost exactly the same, work fine.

tia,
Dries.

 

[mrdenny]

BCP should be loading the data in the order it's listed in the file. BCP doesn't reorder data.

If there is a clustered index (or possibally any index) on the table that you are loading to the data may be displayed in an order different to the order it was loaded in.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DriesM]

thx mrdenny,

but sorry no index at all.
The funny thing is that sometimes the order is correct, but most of the time it isn't, at random.

Some more details:
There are 3 different levels of records in the text file, that's why the order is important.
The text record length isn't fix.
Fields are enclosed by <> so that's though to define a field seperator. A record looks like <******><> ... <**>
That's why I use a single field table varchar(80), which is long enough for all types of records.

format file contains:
8.0
1
1 SQLCHAR 0 80 "\r\n" 1 line SQL_Latin1_General_CP1_CI_AS


DriesM

 

[fredericofonseca]

try and add an identity column to the table, and then retrieve the records with a order by (identity column) to see if they are in the correct order.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DriesM]

Frederico,

I didnt think an identity column would work, but some changes in the format file did the job.
when i retrieve the records afterwards without specifying order by, the id's are still mixed up.
Starting with id 5933, 6017, 6101... finishing with 5932.
But ordering them by id, they look in perfect position.

Many thanks,
DriesM

 

[fredericofonseca]

A select without any ORDER BY is not guaranteed to give you the records on the same order even if you issue the same SELECT over and over.

So if you need a particular order ALWAYS use a order by.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DriesM]

Just one final remark.
By defining an identity column I had to reserve at least one position for it. So I lose the left most character of every record. And even that was only possible after altering the format file manually.
By default bcp chopped the four left most characters (type int).
Fortunatelly I dont really need the first character, because it's the same in every record (<).

DriesM