Need Alternative

The following query is being run on SQL Server 2000, so I do not have the ability to use the OpenRowset (BULK ....) option. I am trying to modify this query so that it does not treat the first record as a header record:

select *
from OpenRowset
( 'MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\aDir; HDR=NO; ' ,
' SELECT * FROM D:\aDir\Jj.TXT '
)

Is my best option to switch this over to a BULK INSERT operation? I am looking for options.

hmm do you have to option to change the contents of the .txt or .csv file?
are you just trying to insert the record to a table from that data source?|||No, I cannot change the contents of the .TXT file. Right now, the process processes the data into columns. The file has tilde characters for column separators but is also each field is fix length.|||do have other process after retrieving the data from the source like you have to update a record on the result set etc.?

or are you just going to insert the result set directly to the database? if yes, you can use BCP instead|||Yes, there is a whole army of processes that run after this process -- updates and selects but little or no deleting|||how bout contention issues? will your process run on multiple users and simultaneously?

imo, i would go with bcp or bulk insert to a table variable or temp table|||No, it is a batch process that runs single thread around 3AM when the server is otherwise quiet. I was looking at bulk insert but I want other opinions and I appreciate you working with me.|||

Kent,

This is a kludge, but ...

Create a file with a single row, dummy values.

Concatenate the data file to the 'header' file. Something like...

c:>\copy DummyHeader.txt /A + DataFile.txt /A NewDataFile.txt /A

|||

Thanks, Arnie. In fact, this is presently what I have done; however, since my input files can be a few gigs this is not my first choice, but indeed, it does work. :-)

|||nice idea arnie. i think that would work too. kent's problem now would lead to comparing performance issues beween bcp, bulk insert and openrowset|||

My other thought on the header issue was to get the mainframe job that creates the file to add a dummy header -- iff I can get the help.

[ :-) You are right: it IS PAINFUL! ]

( Now I gotta scrounge up another 10 GB of space so I can run the test. Can you say COMPRESS? )

|||

get the mainframe job that creates the file to add a dummy header

I feel your pain.

|||Kent,

Give the Jet driver a try. For Jet's text engine, whether or not the first row is treated as a header row depends on a registry entry. If you change it, you should have luck. (I don't recommend using xp_regwrite, but it's easier to post this T-SQL-only solution.)

Code Snippet

exec master..xp_regwrite
@.rootkey='HKEY_LOCAL_MACHINE',
@.key='SOFTWARE\Microsoft\Jet\4.0\Engines\Text',
@.value_name='FirstRowHasNames',
@.type='REG_BINARY',
@.value=0x00
go

select F1 as MyColumnName
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'text; Database=C:\Data;' ,
'SELECT * FROM C:\Data\yourfile.txt'
)

Steve Kass
Drew University
http://www.stevekass.com

|||Excellent information Steve -THANKS!|||

Thanks Steve; I'll give it a roll.

Kent

( That seemed to work fine with all the small test files; not to try it with the large files. )

Well, it works, but it was ... [just caught a nut flush on the river].. significantly slower than the previous version. More testing is required.