Like the bulk insert statement, the bulk copy program (bcp) can be used to load data from a file into a table.
However, unlike the bulk insert statement, bcp can also be used to extract data from a table.
The parameters for the bcp program are similar to the bulk insert statement options. The following MouseOver illustrates the syntax of the bulk copy program.
Bulk Copy Program
When performing bulk copies, you should first know the following information:
When using a query, you can specify any valid SQL statement.
If you are using a query and it produces multiple result sets (either from calling a stored procedure, or having a compute by clause), the bcp will only extract the first result set.
If a data file is missing, the first parameter after the direction ( IN, OUT, FORMAT, QUERYOUT) is treated as the data file name.
The –E parameter has no effect if the file does not contain data for the identity column.
If –n, -c, -w, -6 , -N or –f are not specified, bcp will prompt you for format information about each column.
If one of –n, -c, -w, -6 , -N or –f is specified, a \r will be used as the row terminator and a \t will be used as a field terminator.
In = Import data; out = export data; queryout = you are using a query to export data; format = generate a format file without extracting data--this must be used with the –f option.
bcp Sales.employee in empl.data
-S WesternRegion –T -c
The example above loads data from the file empl.data into the Sales database.
The employee table is on the WesternRegion database server and uses integrated security.
This file has data stored in character format, with a tab between fields and a carriage return at the end of each record.
While bcp allows you to transfer data between SQL Server and files, often times you will want to move data between other data sources. The next
lesson will introduce you to the basics of the data transformation services.
Compute by: A TSQL extension that allows you to have summary rows intermixed with detail rows.