SQL Server Bulk Import - BCP HOW TO

A lot of people using the free MS SQL Server 2005 Express hit a brick wall when they try to import data into the created database. Here is a tutorial, with video demo included on how to use the command-line BCP tool to import data into MS SQL Server 2005 Express.

During an analysis i conducted in the past days, I also found out the hard way that MS SQL Server 2005 Express does not have a GUI based Data Transformation Services. The only thing it does have is a BCP command-line tool.

So, here is a step-by-step tutorial how to use the BCP tool and not give up on an otherwise good (and free) product:

1. The data - I am importing data collected by tcpdump. I stored the data into a CSV file (data.csv), a text file with a comma delimiter.
2. Here is a sample row 16,10.176.1.105,NULL,10.176.1.254,NULL,NULL,64,17.12.2007,19:20:52,520,PING Req,NULL
3. Creating the database - Log-in with the command-line sql tool (sqlcmd) and use the following set of commands to create the database and table for storing of imported data:

* sqlcmd -S ATLAS\SQLEXPRESS
* create database data_analysis
* go
* use data_analysis
* go
* create table data_import (
* [No_packet] [int] NULL ,
* [Src_Logical] [varchar] (255) ,
* [Src_Port] [varchar] (255) ,
* [Dest_Logical] [varchar] (255) ,
* [Dest_Port] [varchar] (255) ,
* [Flags] [varchar] (255) ,
* [Packet_Size] [int] NULL ,
* [Packet_Date] [varchar] (255) ,
* [Absolute_Time] [varchar] (255),
* [Additional] [varchar] (255) ,
* [Protocol] [varchar] (255) ,
* [newdata] [varchar] (255)
* )
* go

Content verification - To verify the contents of the created table, use the following set of commands

* use data_analysis
* select count(*) from data_import
* go
* quit

Data import - To import the data, use the following command

* bcp data_analysis.dbo.data_import in data.csv -T -C1250 -c -t, -S ATLAS\SQLEXPRESS

Detailed explanation

* bcp - the executable file name
* data_analysis.dbo.data_import - name of database, owner and name of table to receive the data
* in - the same command is used for export and import. in means importing, out means exporting
* data.csv - file name that contains data to be imported, or to receive exported data when using the out direction
* -T - swich indicating trusted connection. When using this switch, the bcp command uses the kerberos ticket of the logged-on Windows user to authenticate. If you don't use -T, you'll have to use -U and -P (user name/password)
* -C1250 - collation. I found out that BCP does not work well with Unicode files, so i am forcing the 1250 collation (central European) - works with most characters
* -c - treat everything as characters. This way it will be very easy to import any information.
* -t, - delimiter. Default delimiter for BCP is tab, so i need to inform it of my delimiter character (comma)
* -S ATLAS\SQLEXPRESS - server. This switch is followed by the hostname\instance name (for MS SQL Server Express its SQLEXPRESS)

Content verification - To verify the contents of the created table, use the following set of commands
* use data_analysis
* select count(*) from data_import
* go
* quit