Sorry the information on this page is outdated as it relates to older versions of SQL Server.

We have new content and free videos at this location, http://www.learningcomputer.com/videotraining.html Please note that rest of the SQL Server videos can be bought from this page Buy Tutorials Please send any comments or suggestion to  kashi@learningcomputer.com

bcp (Bulk Copy Program):

bcp is a powerful command line utility provided by SQL Server 6.5. This command copies a database table to or from an operating-system file in a user-specified format. When used correctly, it is the most efficient method for transfer of data to and from a database table.
Syntax for bcp (underlined text is required)

bcp [[database_name.]owner.]table_name {in | out} datafile [/m maxerrors] [/f formatfile] [/e errfile] [/F firstrow] [/L lastrow] [/b batchsize] [/n] [/c] [/E][/t field_term] [/r row_term] [/i inputfile] [/o outputfile] /U login_id [/P password] [/S servername] [/v] [/a packet_size]

where

database_name:Specifies the database

owner:Is the owner's name. This parameter is optional

table_name:Specifies which database table to copy.

in | out:Specifies the direction of the copy. The in option copies from a file into the database table; the
out option copies to a file from the database table.

datafile:Is the full path of an operating-system file when copying a table to or from a hard-disk file

/f formatfile:Is the full path of a file with stored responses from a previous use of bcp on the same table; The default filename is BCP.FMT. If this option is not used, bcp queries you for format information.

/F firstrow:Specifies the number of the first row to copy (the default is the first row).

/L lastrow:Specifies the number of the last row to copy (the default is the last row).

/t field_term:Is the default field terminator.

/r row_term:Is the default row terminator.

/U login_id:Is a login ID.

/S servername:Specifies which SQL Server to connect to.
The command I used in the project was

bcp testDB.dbo.patient in F:\kashef\patient2.txt /Usa /S ACOM /f C:\bcp.fmt /F1 /L100

where

testDB is the name of the database; dbo is the owner of the table; patient is the database table; in specifies that data is being imported to the table; F:\kashef\patient2.txt is the ascii file that contains the data; /Usa specifies the database owner; /S ACOM tells which SQL Server the command will utilize; /f C:\bcp.fmt is the format file that has all the information on field and row terminators; /F1 tells the server to start copying from first row(record); /L100 tells the server to copy upto the 100th row(record)

Additional notes regarding bcp command:

Some of the errors I encountered while working with bcp command

1. Connection Open;DB Library;Unable to connect SQL server
This was one of the first errors I got on bcp. I failed to mention the SQL server before the database name. The SQL server in this case was ACOM. This problem was solved by using the /S ACOM option.

2. Can't allocate space for opject 'Syslogs' in database 'testDB' because the logsegment is full
This error was a result of the Transaction log being full. The transaction log is where all the activity on the database gets recorded. Since you are using bcp, it is recommended to check 'Select into Bulk Copy' and 'Truncate Log on Checkpoint' under options after you double click the database 'testDB'. Also I had to truncate the Transaction Log under Databases. Another option is delete the database if it is empty. The drawback is that you have to create the table structure from the start

3. Violation of primary key constraint. Attempt to insert duplicate key in object 'patient'
The way to get around this problem is to remove the primary key from the table definition, import the data using bulk copy program (bcp), and then add the unique key

4. DB Library: Attempt to convert data stopped by syntax error in source field.
This error was the hardest one to figure out. Apparently the variables in the format file (bcp.fmt) have to be of the type VARCHAR no matter what their original data type was. MS SQL Server requires that these variables be declared as VARCHAR in the format file. After the import, they are converted back into their original type. The only field we had problems with was the dob_patient which was declared at DATETIME.In addition to the date, it had extra digits for hour,min and seconds.

5. The above mention problem with the datetime field was due to the fact that we were importing the file after it was unloaded from a Sybase SQL Central database. Once we unloaded the file using informix, the problem with the datetime field was solved

6. Unexpected end of file (EOF), no rows copied
This error also involves the format file (bcp.fmt) also. After SQL Server reads one record, it looks for an EOF marker. Depending on how the original file was saved, the EOF can be different. In some of the files, it was '~\r\n' while in others it was '\r\n' It has to be just right. Also the col terminator was '~'