BULK INSERT

[phunkydude]

Hi Guys (Gals inferred),

I've got a little headache on my hands!

I've got some text files in a folder, an ISAM text database if you wish. There's an Schema.ini with the schema definition in the same folder.

I want to programmitcally copy the contents of the text files (tables) into SQL Server tables. The problem is that the column orders are different.

I could open two recordsets from different sources; one, the text driver, two, SQL Server. Then transfer row by row (using addnew & update).

That's slow though and the BULK INSERT I'm trying to execute on the SQL Server connection is failing because the text file does not follow the SQL Server Schema.

What u think? Should I create a format file for the BULK INSERT, for each table, keeping things as they are? Should I switch from ADO to SQL-DMO? Has someone out there got any insight in2 this.

Any suggestions would be most welcome and very appreciated.

[Dark Sean]

I think you should use the Bulk Insert like you planned. You should be able to read a definition file programmatically to create the format file you need, but even if you have to type out your format file, it's been my experience that the Bulk insert is the fastest way to enter the data.
Look up "format file" in the msdn library or msdn online, there is a solution for your difference in column orders, here is a paragraph taken from msdn:

"Server column order - Order that columns appear in the SQL Server table. For example, if the fourth field in the data file maps to the sixth column in a SQL Server table, then for the fourth field the server column order is 6.
To omit a column in the table from receiving any data in the data file, set the server column order value to 0. "

Sounds like what you want...

[phunkydude]

Dark Sean,

Ta for the response, I've gone through the MSDN Library already and have finished the code for generating the BCP format files.

I wanted to stick with the BULK INSERT, it's my experience that the operation is much faster.

Don't like hardcoding the Data Type numeric values to text. e.g. 6 == Currency.

Got any suggestions on that one. I'll rate you ;)

<<I sit with a predicament that the client does not have the skills or manpower to manually create the BCP formats. In case you were wondering>>