BCP for bulk inserts into SQL server (SQL 2005)

BCP is a powerful utility to bulk insert data into SQL server. You can use this from the command line thus can be incorporated into a batch file.

I used the format file approach to using BCP. You can generate the format file using the following command line
bcp [CatalogName].[Owner].[TableName] nul -m 10 -f [FormatFileName].fmt -S [ServerName] -T


Finally figured out how to remove the double quotes in a CSV data import file. You will need to change the BCP format file to achieve this. Below is the BCP format file required to do this.

9.0
4
1 SQLCHAR 0 0 "\"" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 50 "\",\"" 1 column1 Latin1_General_CS_AS
3 SQLCHAR 0 4 "\",\"" 2 column2 Latin1_General_CS_AS
4 SQLCHAR 0 300 "\"\r\n" 3 column3 Latin1_General_CS_AS

So the trick is to add an extra fake first row in the BCP format file to strip the very first double quote in the data row of your CSV file. The fake column x has a max data size of 0.

Also you must not have spaces between the rows of the format file.


To execute the BCP use the following command line

bcp [CatalogName].[Owner].[TableName] in [ImportDataFileName].CSV -f [FormatFileName].fmt -S [ServerName] -U [UserName -P [Password]

Make sure paths to all your files are relative to where the BCP is located. BCP is usually found in the following location if you have SQL 2005 C:\Program Files\Microsoft SQL Server\90\Tools\Binn