Hi all-

I have a project I could use some input on, being that I don't have that much experience importing .CSV files into SQL Server.

I need to create a Stored Procedure to import data from .CSV files into SQL Server 2005:

The .CSV files will consist of 2 columns of numeric data. The SQL Server table I am importing into has 5 columns. The 5 columns consist of:

Column 1 - holds the data from column 1 in the .CSV file.

Column 2 - holds the data from column 2 in the .CSV file.

Column 3 - holds metadata about the source of the data, which the user will enter (will be the same for each row in the database table during the current import process).

Column 4 - holds the username of the user who intiated the import process(retrieved programmatically).

Column 5 - holds the date & time that each record was created. Precision to the minute is sufficient, and this data need not be different for each record.

After looking through the docs and some other info, it looks like the best approach is do a BULK INSERT in the Stored Procedure. However, being that there is a difference in structure between the .CSV file and the SQL Server table, it seems I must do one of two things:

1) Use the bcp.exe utility to create a Format File to be used during the BULK INSERT;
Or--
2) Import the data into a temporary table, then move the data from there to the table in which it needs to be inserted, while adding the other data.

Complicating the situation (as usual:-) is a need for speed of execution. I am afraid that any approach that relies on assembling and inserting the data one row at a time will bog down the app significantly; I am anticipating that the database will hold around 10 million records, and each imported file may contain tens of thousands of records.

Anyone have any input? Am I barking up the wrong tree here? Will the approaches suggested above even work? Is there a better, simpler approach?

Thanks for your help,

-Andrew