Chapter 2 - Importing and Exporting Data

Article
01/28/2010

Importing data is the process of retrieving data from sources external to Microsoft SQL Server, for example, an ASCII text file, and inserting the data into SQL Server tables. Exporting data is the process of extracting data from SQL Server into some user-specified format, for example, copying the contents of a SQL Server table to a Microsoft Access database.

Importing data from an external data source into SQL Server is likely to be the first step you perform after setting up your database. After data has been imported into your SQL Server database, you can start to work with the database.

Importing data into SQL Server can be a one-time occurrence; for example, when migrating data from one database system to a SQL Server database because SQL Server is replacing the previous system. After the initial migration is complete, the SQL Server database is used directly for all data-related tasks, rather than the original system. No further data imports may be required.

Importing data can also be an ongoing task. For example, a new SQL Server database is created for executive reporting purposes, but the data resides in legacy systems updated from a large number of business applications. In this case, a daily or weekly import process can be created to copy new or updated data from the legacy system to SQL Server.

Exporting data is usually a less frequent occurrence. SQL Server provides a variety of tools and features that allow applications, such as Access or Microsoft Excel, to connect and manipulate data directly, rather than having to copy all the data from SQL Server to the tool before manipulating. Data may need to be exported from SQL Server regularly if, for example, SQL Server needs to feed data to another business application. In this case, the data can be exported from SQL Server to a text file, and then read from the text file by the application. Alternatively, data can be copied on an ad hoc basis if, for example, a user wanted to extract data from SQL Server into a Excel spreadsheet running on a portable computer, and take the computer on a business trip.

SQL Server provides tools for importing and exporting data to and from a variety of data sources including text files, ODBC data sources (such as Oracle databases), OLE DB data sources (such as other servers running SQL Server), ASCII text files, and Excel spreadsheets.

Additionally, SQL Server replication allows data to be distributed across an enterprise, copying data between locations and synchronizing changes automatically between different copies of data.

Choosing a Tool to Import or Export Data

Data can be imported and exported from Microsoft SQL Server using several tools and Transact-SQL statements. You can also write your own programs to import and export data using the programming models and application programming interfaces (APIs) available with SQL Server.

Methods for copying data to and from SQL Server include:

Using the Data Transformation Services (DTS) Import and Export wizards or DTS Designer to create a DTS package that can be used to import or export data.

The DTS package can also transform data during the import or export process.
Using SQL Server replication to distribute data across an enterprise.

The replication technology in SQL Server allows you to make duplicate copies of your data, move those copies to different locations, and synchronize the data automatically so that all copies have the same data values. Replication can be implemented between databases on the same server or different servers connected by LANs, WANs, or the Internet.

For more information, see "Replication" in Microsoft SQL Server Distributed Data Operations and Replication.
Using the bcp command prompt utility to import and export data between SQL Server and a data file.
Using the BULK INSERT statement to import data from a data file to SQL Server.
Using the SELECT INTO statement to create a new table based on an existing table.

For more information, see "SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference.
It is possible to select data from an arbitrary OLE DB provider, allowing data to be copied from external data sources into SQL Server.
Using the INSERT statement to add data to an existing table.

For more information, see "INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference.
A distributed query that selects data from another data source can also be used to specify the data to be inserted.

For more information, see "Distributed Queries" in Microsoft SQL Server Database Developer's Companion.

The method chosen to import or export data depends on a variety of user requirements, the most common being:

The format of the source and destination data.
The location of the source and destination data.
Whether the import or export is a one-time occurrence or an ongoing task.
Whether a command prompt utility, Transact-SQL statement, or graphical interface is preferred (ease-of-use).
The performance of the import or export operation.

Requiredfunctionality	DTSwizards	Replication	bcp	BULKINSERT	SELECTINTO /INSERT
Import text data	YES		YES	YES	YES1
Export text data	YES		YES
Import from ODBC data sources	YES	YES
Export to ODBC data sources	YES	YES
Import from OLE DB data sources	YES	YES			YES1
Export to OLE DB data sources	YES	YES
Graphical user interface	YES	YES
Command prompt/batch scripts	YES	YES	YES
Transact-SQL scripts		YES		YES	YES
Automatic scheduling	YES	YES	YES2	YES2
Ad hoc import/export	YES		YES	YES	YES
Recurring import/export	YES	YES	YES
Maximum performance			YES	YES
Data transformation	YES
Programmatic interface	YES	YES	YES

1 Using a distributed query retrieving data from an external source by using an OLE DB provider.
2 By explicitly creating a job scheduled using SQL Server Agent.

See Also

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Programming DTS Applications" in Microsoft SQL Server Building Applications

"Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication

Copying Data Using the Data Transformation Services Wizards

The Data Transformation Services (DTS) wizards allow the user to create DTS packages interactively that can be used to import, export, validate, and transform heterogeneous data using OLE DB and ODBC, and copy schema and data between relational databases.

The DTS Import and DTS Export wizards allow the user to:

Specify any custom settings supported by the OLE DB provider used to connect to the data source or destination.
Copy an entire table or the results of an SQL query, such as queries involving joins of multiple tables, or distributed queries.
Build a query using the Query Builder within the wizards. This allows users inexperienced with the SQL language to build queries interactively.
Change the name, data type, size, precision, scale, and nullability of a column when copying the source to the destination (where a valid data type conversion applies).
Specify transformation rules that govern how data is copied between columns of different data type, size, precision, scale, and nullability.
Execute a Microsoft ActiveX® script (Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript®) that can modify (transform) the data when copied from the source to the destination, or perform any operation supported by the VBScript or JScript languages.
Save the DTS package to the Microsoft SQL Server msdb database, Microsoft Repository, or a COM-structured storage file.
Schedule the DTS package for later execution.

For more information about using the DTS Import and DTS Export wizards, see "Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication.

See Also

In Other Volumes

"Exporting Data to a Text File Example" in Microsoft SQL Server Distributed Data Operations and Replication

"Importing Data from an Access Database Example" in Microsoft SQL Server Distributed Data Operations and Replication

"Importing Data from a Text File Example" in Microsoft SQL Server Distributed Data Operations and Replication

"Transferring Data Example" in Microsoft SQL Server Distributed Data Operations and Replication

"Understanding Data Transformation Services" in Microsoft SQL Server Distributed Data Operations and Replication

Copying Data Using bcp or BULK INSERT

The bcp utility (bulk copy program) is a command prompt utility that copies Microsoft SQL Server data to or from a data file. It is used most frequently to transfer large volumes of data into a SQL Server table from another program, usually another database management system. The data to be transferred is first exported from the source program to a data file, and then imported from the data file into a SQL Server table using bcp. Alternatively, bcp can be used to transfer data from a SQL Server table to a data file for use in other programs. For example, the data can be copied from SQL Server into a data file; from there, another program can import the data.

Note The bcp utility is written using the ODBC bulk copy application programming interface (API). Earlier versions of the bcp utility were written using the DB-Library bulk copy API.

Data can also be transferred into a SQL Server table from a data file using the BULK INSERT statement; however, the BULK INSERT statement cannot bulk copy data from SQL Server to a data file. The BULK INSERT statement allows you to bulk copy data into SQL Server using the functionality of the bcp utility with a Transact-SQL statement rather than from the command prompt.

It is also possible to write programs to bulk copy SQL Server data to or from a data file using the bulk copy API. The bulk copy API can be used in ODBC, OLE DB, SQL-DMO, and DB-Library based applications.

See Also

In Other Volumes

"Backward Compatibility Details (Level 2)" in Microsoft SQL Server Introduction

"Bulk-Copy Functions" in Microsoft SQL Server Building Applications

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Bulk-Copy Rowsets" in Microsoft SQL Server Building Applications

"Performing Bulk Copy Operations" in Microsoft SQL Server Building Applications

"BulkCopy Object" in Microsoft SQL Server Distributed Management Objects

Running the bcp Utility

The data file containing the imported data needs to be in row/column format so that the bcp utility can understand how the data is to be inserted. Microsoft SQL Server can accept data in any ASCII or binary format as long as the terminators (characters used to separate columns and rows) can be described. The structure of the data file need not be identical to the structure of the SQL Server table because bcp allows columns to be skipped or reordered during the bulk copy process.

Data bulk copied into SQL Server is appended to any existing contents of a table; data bulk copied from SQL Server to a data file overwrites the previous contents of the data file.

The prerequisites for bulk copying data are:

The destination must already exist.

The number of fields in the data file do not have to match the number of columns in the table or be in the same order.
The data in the data file must be text-only (character) format or a format generated previously by the bcp utility, such as native format.

Each column in the table must be compatible with the field in the data file being copied. For example, it is not possible to copy an int field to a datetime column using native format bcp.
Relevant permissions to bulk copy data are required on source and destination tables.

To bulk copy data from a data file into a table, you must have INSERT and SELECT permission on the table. To bulk copy a table or view to a data file, you must have SELECT permission on the table or view being bulk-copied.

Importing and Exporting Data Example

To bulk copy data from the publishers table in the pubs database to the Publishers.txt data file in ASCII text format, execute from the command prompt:

bcp pubs..publishers out publishers.txt -c -Sservername -Usa -Ppassword

The contents of the Publishers.txt file:

0736	New Moon Books	Boston	MA	USA
0877	Binnet & Hardley	Washington	DC	USA
1389	Algodata Infosystems	Berkeley	CA	USA
1622	Five Lakes Publishing	Chicago	IL	USA
1756	Ramona Publishers	Dallas	TX	USA
9901	GGG&G	München		Germany
9952	Scootney Books	New York	NY	USA
9999	Lucerne Publishing	Paris		France

Conversely, to bulk copy data from the Publishers.txt file into the publishers2 table in the pubs database, execute from the command prompt:

bcp pubs..publishers2 in publishers.txt -c -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..publishers2 FROM 'c:\publishers.txt'  WITH (DATAFILETYPE = 'char')

Note The publishers2 table needs to be created first.

See Also

In This Volume

Native Format bcp

Character Format bcp

Managing Security Accounts

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ImportData Method" in Microsoft SQL Server Distributed Management Objects

"ExportData Method" in Microsoft SQL Server Distributed Management Objects

Native, Character, and Unicode Formats

The bcp utility can create or read data files in four default data formats by specifying a parameter at the command prompt.

Data format	bcp utility parameter	BULK INSERT clause
Native	-n	DATAFILETYPE = 'native'
Character	-c	DATAFILETYPE = 'char'
Unicode character	-w	DATAFILETYPE = 'widechar'
Unicode native	-N	DATAFILETYPE = 'widenative'

By default, the bcp utility operates in interactive mode and queries Microsoft SQL Server for further information needed to specify the data format. However, when using the -n, -c, -w, or -N parameters, bcp does not query for information about the SQL Server table on a column-by-column basis; it reads or writes the data using the default format specified.

By default, the BULK INSERT statement operates in character mode (char); interactive mode is not applicable.

Additionally, the -6 parameter causes the bcp utility to modify native (-n) or character (-c) data to a format compatible with SQL Server version 6.0 or 6.5 clients.

The recommended default data format used depends on the type of bulk copy operation that needs to be performed.

Bulk copy operation	Native	Character	Unicodecharacter	Unicodenative
Bulk copying data from SQL Server to SQL Server using a data file (no extended/DBCS characters involved).	YES1
Bulk copying data from SQL Server to SQL Server using a data file (extended/DBCS characters involved).				YES
Exporting data to a text file to be used in another program.		YES
Importing data from a text file generated by another program.		YES
Bulk copying data from SQL Server to SQL Server using a data file (Unicode data/no extended/DBCS characters).			YES

1 Fastest method for bulk copying data from SQL Server using bcp.

Note The -6 parameter is not applicable to the BULK INSERT statement.

See Also

In This Volume

Interactive bcp

Native Format bcp

The -n parameter (or native value for the DATAFILETYPE clause of the BULK INSERT statement) uses native (database) data types. Storing information in native format is useful when information is to be copied from one computer running Microsoft SQL Server to another. Using native format saves time and space, preventing unnecessary conversion of data types to and from character format. However, a data file in native format cannot be read by any program other than bcp.

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt data file using native data format is:

bcp pubs..publishers out publ.txt -n -Sservername -Usa -Ppassword

The bcp utility adds an ASCII character to the beginning of each char or varchar field equivalent to the length of the data in those fields. Noncharacter data in the table is written to the data file in the SQL Server internal binary data format.

Note Native format can now be used to bulk copy data from one computer running SQL Server to another running with a different processor architecture (by means of a data file). This was not possible with earlier versions of SQL Server.

Important Using native mode, bcp, by default, always converts characters from the data file to ANSI characters before bulk copying them into SQL Server, and converts characters from SQL Server to OEM characters before copying them to the data file. This can cause loss of extended character data during the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended characters, use Unicode native format, or specify a code page for the bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT statement).

See Also

In This Volume

Copying Data Between Different Code Pages

Unicode Native Format bcp

In Other Volumes

"ServerBCPDataFileType Property" in Microsoft SQL Server Distributed Management Objects

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Character Format bcp

The -c parameter (or char value for the DATAFILETYPE clause of the BULK INSERT statement) uses the character (char) data format for all columns, providing tabs between fields and a newline character at the end of each row as default terminators. Storing information in character format is useful when the data is used with another program, such as a spreadsheet, or when the data needs to be copied into Microsoft SQL Server from another database. Character format tends to be used when copying data from other programs because they have the functionality to export and import data in plain text format.

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt data file using character format is:

bcp pubs..publishers out publ.txt -c -Sservername -Usa -Ppassword

The contents of the Publ.txt file:

0736	New Moon Books	Boston	MA	USA
0877	Binnet & Hardley	Washington	DC	USA
1389	Algodata Infosystems	Berkeley	CA	USA
1622	Five Lakes Publishing	Chicago	IL	USA
1756	Ramona Publishers	Dallas	TX	USA
9901	GGG&G	München		Germany
9952	Scootney Books	New York	NY	USA
9999	Lucerne Publishing	Paris		France

To use field and row terminators other than the default provided with character format, specify the following.

Terminator	bcp utility parameter	BULK INSERT clause
Field	-t	FIELDTERMINATOR
Row	-r	ROWTERMINATOR

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt data file using character format, with a comma as a field terminator and the newline character (\n) as the row terminator is:

bcp pubs..publishers out publ.txt -c -t , -r \n -Sservername -Usa -Ppassword

The contents of the Publ.txt file:

0736,New Moon Books,Boston,MA,USA 0877,Binnet & Hardley,Washington,DC,USA 1389,Algodata Infosystems,Berkeley,CA,USA 1622,Five Lakes Publishing,Chicago,IL,USA 1756,Ramona Publishers,Dallas,TX,USA 9901,GGG&G,München,Germany 9952,Scootney Books,New York,NY,USA 9999,Lucerne Publishing,Paris,France

Important Using character mode, bcp, by default, always converts characters from the data file to ANSI characters before bulk copying them into SQL Server, and converts characters from SQL Server to OEM characters before copying them to the data file. This can cause loss of extended character data during the OEM to ANSI or ANSI to OEM conversions. To prevent loss of extended characters, use Unicode character format, or specify a code page for the bulk copy operation using -C (or the CODEPAGE clause for the BULK INSERT statement).

See Also

In This Volume

Copying Data Between Different Code Pages

In Other Volumes

"ServerBCPDataFileType Property" in Microsoft SQL Server Distributed Management Objects

Modifying Native and Character Format bcp Using -6

The -6 parameter, when used in conjunction with either native format (-n) or character format (-c), uses Microsoft SQL Server version 6.0 or 6.5 data types. Use this option when data files contain values using SQL Server 6.5 formats, such as data files generated by the bcp utility supplied with SQL Server 6.5 and earlier. For example, to bulk copy into SQL Server date formats supported by earlier versions of the bcp utility but no longer supported by ODBC, use the -6 parameter.

The -6 parameter must be used for data files generated by an earlier version of bcp because nulls are represented differently. Earlier versions of bcp represented null values as a length value of 0, whereas null is now stored as the length value -1. The value 0 now represents a zero-length column. Additionally, any date values formatted using DB-Library date formats can be read from a data file by bcp if -6 is specified.

Important When bulk copying data from SQL Server into a data file specifying -6:

bcp does not generate SQL Server 6.0 or 6.5 date formats for any datetime or smalldatetime data. Dates are always written in ODBC format.
Null values in bit columns are written as the value 0 because SQL Server 6.5 and earlier does not support nullable bit data.

The bcp utility adds an ASCII character to the beginning of each data file field equivalent to the length of the data in char or varchar fields. In a table with numeric data, the information is written to the data file in the SQL Server internal binary data format.

Note The -6 parameter is not applicable to the BULK INSERT statement.

See Also

In This Volume

Copying Date and Money Values

In Other Volumes

"Use6xCompatible Property" in Microsoft SQL Server Distributed Management Objects

Unicode Character Format bcp

The -w parameter (or widechar value for the DATAFILETYPE clause of the BULK INSERT statement) uses the Unicode character data format for all columns, providing tabs between fields and a newline character at the end of each row as default terminators. This allows data to be copied both from a server using a code page different from the code page used by the client running bcp, and to another server with the same (or a different) code page as the original server:

Without loss of any character data if the source and destination are Unicode data types.
With minimal loss of extended characters in the source data that cannot be represented at the destination if the source and destination are not Unicode data types.

For example, the command to bulk copy the publishers table in the pubs database to the Publ.txt file using Unicode character format is:

bcp pubs..publishers out publ.txt -w -Sservername -Usa -Ppassword

Unicode character format data files follow the conventions for Unicode files, whereby the first two bytes of the file are either of the hexadecimal numbers 0xFEFF or 0xFFFE. These bytes serve as byte-order marks, specifying whether the high-order byte is stored first or last in the file.

To use field and row terminators other than the default provided with Unicode character format, specify the following.

Terminator	bcp utility parameter	BULK INSERT clause
Field	-t	FIELDTERMINATOR
Row	-r	ROWTERMINATOR

For example, the command to bulk copy the publishers table to the Publ.txt data file using Unicode character format, with a comma as a field terminator and the newline character (\n) as the row terminator is:

bcp pubs..publishers out publ.txt -w -t , -r \n -Sservername -Usa -Ppassword

Examining the contents of the Publ.txt data file, two character positions are used for each character in the data, with each field separated by a comma, and each row separated by a newline character.

See Also

In Other Volumes

"ServerBCPDataFileType Property" in Microsoft SQL Server Distributed Management Objects

Unicode Native Format bcp

The -N parameter (or widenative value for the DATAFILETYPE clause of the BULK INSERT statement) uses native (database) data types for all noncharacter data, and Unicode character data format for all character (char, nchar, varchar, nvarchar, text, and ntext) data.

Storing information in Unicode native format is useful when information is to be copied from one Microsoft SQL Server installation to another. Using native format for noncharacter data saves time, preventing unnecessary conversion of data types to and from character format. Using Unicode character format for all character data prevents loss of any extended characters when bulk loading data between servers using different code pages. However, a data file in Unicode native format can be read only by the bcp utility and the BULK INSERT statement.

For example, the command to bulk copy the sales table in the pubs database to the Sales.dat data file using Unicode native data format is:

bcp pubs..sales out Sales.dat -N -Sservername -Usa -Ppassword

See Also

In Other Volumes

"ServerBCPDataFileType Property" in Microsoft SQL Server Distributed Management Objects

Interactive bcp

If data is being copied between Microsoft SQL Server and other programs, such as another database program, the default data type formats (native, character, or Unicode) may not be compatible with the data structures expected by the other programs. Therefore, the bcp utility allows more detailed information regarding the structure of the data file to be specified.

If the -n, -c, -w, or -N parameters are not specified, the bcp utility prompts for further information interactively on each column of data being copied:

File storage type
Prefix length
Field length
Field terminator

Note Interactive mode is not available when using the BULK INSERT statement.

The bcp utility provides default values at each of these prompts based on the SQL Server data type of the source or destination column. Accepting the default values supplied by bcp at these prompts produces the same result as native format (-n), and provides a way to bulk copy data out of other programs for later reloading into SQL Server.

A format file can be created to store the responses of the prompts for each field in the data file, allowing the same responses to be reused without having to enter them again. The format file can be used to provide all the format information required to bulk copy data to and from SQL Server. A format file provides a flexible system for writing data files that requires little or no editing to conform to other data formats, or for reading data files from other software.

For example, the command to bulk copy the publishers table interactively to the Publ.txt file is:

bcp pubs..publishers out publ.txt -Sservername -Usa -Ppassword

A series of four prompts appears for each column of the publishers table, with the bcp-supplied default displayed in brackets. This example is for the pub_id column in the publishers table only.

Enter the file storage type of field pub_id [char]: Enter prefix length of field pub_id [0]: Enter length of field pub_id [4]: Enter field terminator [none]:

Pressing ENTER accepts the supplied default. To specify a value other than the default, enter the new value at the command prompt.

See Also

In This Volume

Using the bcp Format File

File Storage Type

The file storage type describes how data is stored in the data file. Data can be copied to a data file as its database table type (native format), as a character string in ASCII format (character format), or as any data type where implicit conversion is supported (for example, copying a smallint as an int). User-defined data types are copied as their base types.

To bulk copy data from Microsoft SQL Server to a data file in the most compact storage possible (native data format), accept the default file storage types provided by bcp.

To bulk copy data from SQL Server to a data file as ASCII text, specify char as the file storage type for all columns in the table.

To bulk copy data to SQL Server from a data file, specify the file storage type as char for ASCII-only files, and the following appropriate file storage type for data stored in native data type format.

File storage type	Enter at command prompt
char	c[har]
varchar	c[har]
nchar	w
nvarchar	w
text	T[ext]
ntext	W
binary	x
varbinary	x
image	I[mage]
datetime	d[ate]
smalldatetime	D
decimal	n
numeric	n
float	f[loat]
real	r
int	i[nt]
smallint	s[mallint]
tinyint	t[inyint]
money	m[oney]
smallmoney	M
bit	b[it]
uniqueidentifier	u
timestamp	x

Entering a file storage type that represents an invalid implicit conversion causes bcp to fail. For example, specifying smallint for int data causes overflow errors, but specifying int for smallint data is valid. Specifying char as the file storage type when bulk copying any data type from SQL Server to a data file is always valid.

When noncharacter data types (for example, float, money, datetime, or int) are stored as their database types, the data is written to the data file in the SQL Server internal binary data format.

A format file can also be generated to save the responses of the file storage type for each field. This format file can be used to provide the default information used to bulk copy the data in the data file back into SQL Server, or to bulk copy data out from the table another time, without needing to respecify the format. Each native file storage type is recorded in the format file as a corresponding host file data type.

File storage type	Host file data type
char	SQLCHAR
varchar	SQLCHAR
nchar	SQLNCHAR
nvarchar	SQLNCHAR
text	SQLCHAR
ntext	SQLNCHAR
binary	SQLBINARY
varbinary	SQLBINARY
image	SQLBINARY
datetime	SQLDATETIME
smalldatetime	SQLDATETIM4
decimal	SQLDECIMAL
numeric	SQLNUMERIC
float	SQLFLT8
real	SQLFLT4
int	SQLINT
smallint	SQLSMALLINT
tinyint	SQLTINYINT
money	SQLMONEY
smallmoney	SQLMONEY4
bit	SQLBIT
uniqueidentifier	SQLUNIQUEID
timestamp	SQLBINARY

Because data files stored as ASCII text use char as the file storage type, only SQLCHAR appears in the format file in those instances.

See Also

In This Volume

Using the bcp Format File

Prefix Length

To provide the most compact file storage when bulk copying data in native format to a data file, bcp precedes each field with one or more characters that indicates the length of the field. These characters are called length prefix characters. The number of length prefix characters required is called the prefix length.

The number of length prefix characters required to store the length of the data field depends on the file storage type, the nullability of a column, and whether the data is being stored in the data file in its native (database) data type or as ASCII characters (character format). A text data type requires four prefix characters to store the field length, whereas a binary data type requires two characters.

Note These length prefix characters are stored in the data file in Microsoft SQL Server internal binary data format.

Null values are represented as an empty field when copied from SQL Server to a data file. To indicate that the field is empty (represents NULL), the field prefix contains the value -1. Any SQL Server column that allows null values needs field prefix characters, and requires a prefix length of 1 or greater, depending on the file storage type.

Use these prefix lengths when bulk copying data from SQL Server to a data file, storing the data using either native data types or as ASCII characters (text file).

data type	NOT NULL	NULL	NOT NULL	NULL
char	2	2	2	2
varchar	2	2	2	2
nchar	2	2	2	2
nvarchar	2	2	2	2
text	4	4	4	4
ntext	4	4	1	1
binary	1	1	2	2
varbinary	1	1	2	2
image	4	4	4	4
datetime	0	1	1	1
smalldatetime	0	1	1	1
decimal	1	1	1	1
numeric	1	1	1	1
float	0	1	1	1
real	0	1	1	1
int	0	1	1	1
smallint	0	1	1	1
tinyint	0	1	1	1
money	0	1	1	1
smallmoney	0	1	1	1
bit	0	1	0	1
uniqueidentifier	1	1	1	1
timestamp	1	1	2	2

When storing data as nchar rather than char, the prefix length for all data types is the same as the native data type value, except char, varchar, text, ntext, and image, which all have a prefix length of 1.

When bulk copying data to SQL Server, the prefix length is the value specified when the data file was created originally. If the data file was not created with bcp, it is unlikely that length prefix characters exist. In this instance, specify 0 for the prefix length.

Note The default values provided at the prompts indicate the most efficient prefix lengths.

Field Length

When bulk copying char, nchar, or binary data with a prefix length of 0 from Microsoft SQL Server, bcp also prompts for a field length. The field length indicates the maximum number of characters needed to represent data in character format. A column of type tinyint can have values from 0 through 255; the maximum number of characters needed to represent any number in that range is three (representing values 100 through 255). When bcp converts noncharacter data to character, it suggests a default field length large enough to store the data.

If the file storage type is noncharacter, data is stored in the SQL Server native data representation (native format) and the bcp utility does not prompt for a field length.

These are the default field lengths for data to be stored as char file storage type (nullable data is the same length as nonnull data).

Data type	Default length (characters)
char	Length defined for the column
varchar	Length defined for the column
nchar	Twice the length defined for the column
nvarchar	Twice the length defined for the column
text	0
ntext	0
bit	1
binary	Twice the length defined for the column + 1
varbinary	Twice the length defined for the column + 1
image	0
datetime	24
smalldatetime	24
float	30
real	30
int	12
smallint	7
tinyint	5
money	30
smallmoney	30
decimal	41*
numeric	41*
uniqueidentifier	37
timestamp	17

*For more information about the decimal and numeric data types, see "decimal and numeric" in Microsoft SQL Server Transact-SQL and Utilities Reference.

These are the default field lengths for data to be stored as native file storage type (nullable data is the same length as nonnull data, and character data is always stored in character format).

Data type	Default length (characters)
bit	1
binary	Length defined for the column
varbinary	Length defined for the column
image	0
datetime	8
smalldatetime	4
float	8
real	4
int	4
smallint	2
tinyint	1
money	8
smallmoney	4
decimal	*See footnote
numeric	*See footnote
uniqueidentifier	16
timestamp	8

*For information about the decimal and numeric data types, see "decimal and numeric" in Microsoft SQL Server Transact-SQL and Utilities Reference.

Accepting the bcp default values for the field length is recommended.

Specifying a field length too short for numeric data when bulk copying data causes bcp to print an overflow message and not copy the data. When datetime data is copied to a data file as a character string of less than 26 bytes, the data is truncated without an error message. When creating an ASCII data file, use the default field length to ensure that data is not truncated and that numeric overflow errors causing bcp to fail do not occur. To change the default field length, supply another value.

Note To create a data file for later reloading into SQL Server and keep the storage space to a minimum, use a length prefix character with the default file storage type and the default field length.

The amount of storage space allocated in the data file for noncharacter data stored as char file storage type also depends on whether a prefix length or terminators are specified:

If specifying a prefix length of 1, 2, or 4, the field length is not used. The data file storage space used is the length of the data, the length of the prefix, plus any terminators.
If specifying a prefix length of 0 and no terminator, bcp allocates the maximum amount of space shown in the field length prompt because this is the maximum space that may be needed for the data type in question. The field is treated as if it were of fixed length so that it is possible to determine where one field ends and the next begins.
If specifying a prefix length of 0 and a terminator, the field length specified is ignored. The data file storage space used is the length of the data, plus any terminators.

SQL Server char data is always stored in the data file as the full length of the defined column. For example, a column defined as char(10) always occupies 10 characters in the data file regardless of the length of the data stored in the column; spaces are appended to the data as padding. For more information, see "SET ANSI_PADDING" in Microsoft SQL Server Transact-SQL and Utilities Reference.

The interaction of prefix lengths (P), terminators (T), and field length on data determines the storage space used in the data file. In this example, the field length is 8 for each column, and the 6-character value "string" is stored each time. Dashes (-) indicate appended spaces and ellipses (...) indicate that the pattern repeats for each field.

This is the pattern for SQL Server char data.

	Prefix length = 0	Prefix length = 1, 2, or 4
No terminator	string--string--...	Pstring--Pstring--...
Terminator	string--Tstring--T...	Pstring--TPstring--T...

This is the pattern for other data types converted to char storage.

	Prefix length = 0	Prefix length = 1, 2, or 4
No terminator	string--string--...	PstringPstring...
Terminator	stringTstringT...	PstringTPstringT...

Field Terminator

It is possible to use optional terminating characters to mark the end of a field or row, separating one field or row in the data file from the next. Terminating characters indicate to a program reading the data file where one field or row ends and another begins. The default provided by the bcp utility is to use no terminating characters between fields and rows in the data file.

Field terminators are needed when the data file does not contain:

Length prefixes to indicate the length of each field (perhaps because the program reading the data file does not understand length prefixes).
Fixed-length data fields (perhaps because storage space needs to be minimized).

The bcp utility allows many characters to be used as field or row terminators.

Terminator	Indicated by
Tab	\t
Newline character	\n
Carriage return	\r
Backslash	\\
Null terminator (no visible terminator)	\0
Any printable character (control characters are not printable, except null, tab, newline, and carriage return)	(*, A, t, l, and so on)
String of up to 10 printable characters, including some or all of the terminators listed earlier	(\t, end, !!!!!!!!!!, \t--\n, and so on)

Note Only the t, n, r, \, and 0 characters work with the backslash escape character to produce a control character.

It is possible to change the default field and row terminators using the -t and -r parameters of bcp. When using these parameters, the bracketed default listed in the interactive bcp prompt changes for all fields and rows to the value specified at the command prompt. Use -t to change the default field terminator, and -r to change the default row terminator.

The command to change the default field terminator to a comma (,), and the default row terminator to the newline character (\n):

bcp pubs..publishers out publ.txt -t , -r \n -Sservername -Usa -Ppassword

Important Terminators must be chosen to ensure that their pattern does not appear in any of the data. For example, when using tab terminators with a field that contains tabs as part of the data, bcp does not know which tab represents the end of the field. The bcp utility always looks for the first possible character(s) that matches the terminator it expects. Using a character sequence with characters that do not occur in the data avoids this conflict.

Native format data can also conflict with terminators because this file is in the Microsoft SQL Server internal binary data format. When using native format, use length prefixes rather than field terminators.

Any data column that contains null values is considered variable length for bulk copy purposes. Therefore, a length prefix or field terminator needs to be used to specify the length of each field.

Note The no terminator value is different from the null terminator (\0) value. The no terminator value places no row terminator character(s). The null terminator value puts a null character after the column. A null character is invisible but real.

Because bcp does not prompt for a row terminator, the field terminator for the last column in a row serves that purpose. Given a row with 10 columns, the field terminator for the tenth column is also the row terminator. Therefore, the terminator for the last field can be (but is not required to be) different from the field terminator used for other fields in the same row. For tabular output, terminate the last field with the newline character (\n) and all other fields with the tab character (\t).

A common row terminator used when exporting SQL Server data to ASCII data files is \r\n (carriage return, newline). Using both characters as the row terminator ensures that each row of data appears on its own line in the data file. However, it is only necessary to enter the characters \r\n as the terminator when manually editing the terminator column of a bcp format file. When you use bcp interactively and specify \n (newline) as the row terminator, bcp prefixes the \r (carriage return) character automatically.

See Also

In Other Volumes

"ColumnDelimiter Property" in Microsoft SQL Server Distributed Management Objects

"RowDelimiter Property" in Microsoft SQL Server Distributed Management Objects

Using the bcp Format File

When bulk copying data using interactive mode, the bcp utility prompts you to store information regarding the storage type, prefix length, field length, and field and row terminators. The file used to store the format information for each field in the data file is called the format file:

Do you want to save this format information in a file? [Y/n] y Host filename: [bcp.fmt]

Although the default name for the format file is Bcp.fmt, a different file name can be specified.

This format file provides the default information used either to bulk copy the data in the data file back into Microsoft SQL Server or to bulk copy data out from the table another time, without needing to respecify the format. When bulk copying data into or out of SQL Server with an existing format file, bcp does not prompt for the file storage type, prefix length, field length, or field terminator because it uses the values already recorded.

To use a previously created format file when importing data into SQL Server, use the -f parameter with the bcp utility or the FORMATFILE clause with the BULK INSERT statement. For example, the command to bulk copy the contents of New_auth.dat data file into the authors2 table in the pubs database using the previously created format file (Authors.fmt) is:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword

The BULK INSERT statement can use format files saved by the bcp utility. For example:

BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat'  WITH (FORMATFILE = 'c:\authors.fmt')

The format file is a text file with a specific structure.

Cc917564.fig5_1(en-us,TechNet.10).gif

Field	Description
Version	Version number of bcp.
Number of fields	Number of fields in the data file.
Host file field order	Position of each field within the data file. The first field in the row is 1, and so on.
Host file data type	Data type stored in the particular field of the data file. With ASCII data files, use SQLCHAR; for native format data files, use default data types. For more information, see "File Storage Type" in this volume.
Prefix length	Number of length prefix characters for the field. Legal prefix lengths are 0, 1, 2, and 4. To avoid specifying the length prefix, set this to 0. A length prefix must be specified if the field contains null data values. For more information, see "Prefix Length" in this volume.
Host file data length	Maximum length, in bytes, of the data type stored in the particular field of the data file. For more information, see "Field Length" in this volume.
Terminator	Delimiter to separate the fields in a data file. Common terminators are comma (,), tab (\t), and end of line (\r\n). For more information, see "Field Terminator" in this volume.
Server column order	Order that columns appear in the SQL Server table. For example, if the fourth field in the data file maps to the sixth column in a SQL Server table, then for the fourth field the server column order is 6.To omit a column in the table from receiving any data in the data file, set the server column order value to 0.
Server column name	Name of the column taken from the SQL Server table. It is not necessary to use the actual name of the field. The only condition is that the field in the format file not be blank.

Note It is possible to skip importing a table column if the field does not exist in the data file by specifying 0 prefix length, 0 length, 0 server column order, and no terminator. This effectively states that the data field does not exist in the data file, and that the server column should not have data loaded into it. Columns cannot be skipped on output.

Selectively Copying Data

A format file provides a way to bulk copy data selectively from a data file to SQL Server. This allows the transfer of data to a table when there is a mismatch between fields in the data file and columns in the table. This approach can be used when the fields in the data file are:

Fewer than the columns in the table.
More than the columns in the table.
In a different order from the columns in the table.

By using a format file, it is possible to bulk copy data into SQL Server without having to add or delete unnecessary, or reorder existing, data in the data file.

The following three topics contain examples of selectively copying data. For the following examples, first make a copy of the authors table, named authors2, in the pubs database. To create a copy of the authors table, execute:

USE pubs GO sp_dboption pubs, 'select into/bulkcopy', 'true' GO SELECT * INTO authors2 FROM authors GO

See Also

In Other Volumes

"FormatFilePath Property" in Microsoft SQL Server Distributed Management Objects

Using a Data File with Fewer Fields

In this example, the New_auth.dat data file (ASCII, or character format) does not contain matching fields for the address and zip columns in the authors2 table.

The New_auth.dat file:

777-77-7777,Smith,Chris,303 555-1213,Denver,CO,1 888-88-8888,Doe,John,206 555-1214,Seattle,WA,0 999-99-9999,Door,Jane,406 555-1234,Bozeman,MT,1

To bulk copy data selectively to the correct columns in authors2, create a default format file (Authors.fmt) with the following command:

bcp pubs..authors2 out c:\authors.txt -Sservername -Usa -Ppassword

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of authors2. The field terminator for every column should be a comma (,), except for the contract column, which should use the row terminator \n (newline) because it is the last column in the row. Also, the contract column has a file storage type of char because the data file is an ASCII file. The address and zip columns should not have field terminators, and should have their field length set to 0. When prompted for the format file name, specify Authors.fmt.

The Authors.fmt file:

7.0 9

1	SQLCHAR	0	11	","	1	au_id
2	SQLCHAR	0	40	","	2	au_lname
3	SQLCHAR	0	20	","	3	au_fname
4	SQLCHAR	0	12	","	4	phone
5	SQLCHAR	0	0	""	5	address
6	SQLCHAR	0	20	","	6	city
7	SQLCHAR	0	2	","	7	state
8	SQLCHAR	0	0	""	8	zip
9	SQLCHAR	0	1	"\r\n"	9	contract

The format file contains all the information necessary to bulk copy data from the data file to the Microsoft SQL Server table. A prefix length of 0, field length of 0, and no field terminator for address and zip means that these columns do not exist in the data file. However, the format file must be modified further with a text editor to ensure that no data will be loaded into address and zip. The server column numbers (sixth field in the format file) for these columns should be 0:

7.0 9

1	SQLCHAR	11	","	1	au_id
2	SQLCHAR	40	","	2	au_lname
3	SQLCHAR	20	","	3	au_fname
4	SQLCHAR	12	","	4	phone
5	SQLCHAR	0	""	0	address
6	SQLCHAR	20	","	6	city
7	SQLCHAR	2	","	7	state
8	SQLCHAR	0	""	0	zip
9	SQLCHAR	1	"\r\n"	9	contract

The data in the data file can now be bulk copied into authors2 using the command:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat'  WITH (FORMATFILE = 'c:\authors.fmt')

Note Because address and zip are not present in the data file, those columns will contain NULL in the SQL Server table. Therefore, authors2 must allow null values in those columns.

Using a Data File with More Fields

In this example, the New_auth.dat data file (ASCII, or character format) contains two fields (age and salutation) that the authors2 table does not contain. These fields will be omitted, or skipped, during the bulk copy procedure.

The New_auth.dat file:

777-77-7777,Smith,Chris,303 555-1213,27 College Ave,Denver,CO,80220,1,28,Ms. 888-88-8888,Doe,John,206 555-1214,123 Maple Street,Seattle,WA,95099,0,35,Mr. 999-99-9999,Door,Jane,406 555-1234,45 East Main,Bozeman,MT,59715,1,33,Mrs.

To bulk copy data selectively to the correct columns in authors2 only, create a default format file (Authors.fmt) with the command:

bcp pubs..authors2 out c:\authors.txt -Sservername -Usa -Ppassword

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of authors2. The field terminator for every column should be a comma (,). Also, the contract column has a file storage type of char because the data file is an ASCII file. When prompted for the format file name, specify Authors.fmt.

The Authors.fmt file:

7.0 9

1	SQLCHAR	11	","	1	au_id
2	SQLCHAR	40	","	2	au_lname
3	SQLCHAR	20	","	3	au_fname
4	SQLCHAR	12	","	4	phone
5	SQLCHAR	40	","	5	address
6	SQLCHAR	20	","	6	city
7	SQLCHAR	2	","	7	state
8	SQLCHAR	5	","	8	zip
9	SQLCHAR	1	"\r\n"	9	contract

The format file contains all the information necessary to bulk copy data from the data file to the Microsoft SQL Server table. However, the format file needs to be modified further with a text editor to reflect the addition of two new columns: age and salutation. The second line of the format file specifies the number of columns and should now be changed to 11 because there are 11 fields in the data file. Two new rows need to be added to the end of the format file to provide format information for the additional fields. The row terminator needs to be moved from the contract column to the salutation column and the server column numbers (sixth field in the format file) for the age and salutation columns should be 0:

7.0 11

1	SQLCHAR	0	11	","	1	au_id
2	SQLCHAR	0	40	","	2	au_lname
3	SQLCHAR	0	20	","	3	au_fname
4	SQLCHAR	0	12	","	4	phone
5	SQLCHAR	0	40	","	5	address
6	SQLCHAR	0	20	","	6	city
7	SQLCHAR	0	2	","	7	state
8	SQLCHAR	0	5	","	8	zip
9	SQLCHAR	0	1	","	9	contract
10	SQLCHAR	0	0	","	0	age
11	SQLCHAR	0	0	"\r\n"	0	salutation

The data in the data file can now be bulk copied into authors2 using the command:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat'  WITH (FORMATFILE = 'c:\authors.fmt')

Using a Data File with Fields in a Different Order

In this example, the New_auth.dat data file (ASCII, or character format) contains the same number of fields as the authors2 table, but the au_lname and au_fname fields are reversed. These fields will be reordered during the bulk copy procedure.

The New_auth.dat file:

777-77-7777,Chris,Smith,303 555-1213,27 College Ave,Denver,CO,80220,1 888-88-8888,John,Doe,206 555-1214,123 Maple Street,Seattle,WA,95099,0 999-99-9999,Jane,Door,406 555-1234,45 East Main,Bozeman,MT,59715,1

To bulk copy data selectively to the correct columns in authors2, create a default format file (Authors.fmt) with the command:

bcp pubs..authors2 out c:\authors.txt -Sservername -Usa -Ppassword

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of authors2. The field terminator for every column should be a comma (,), except for the contract column, which should use the row terminator \n (newline) because it is the last column in the row. Also, the contract column has a file storage type of char because the data file is an ASCII file. When prompted for the format file name, specify Authors.fmt.

The Authors.fmt file:

7.0 9

1	SQLCHAR	0	11	","	1	au_id
2	SQLCHAR	0	40	","	2	au_lname
3	SQLCHAR	0	20	","	3	au_fname
4	SQLCHAR	0	12	","	4	phone
5	SQLCHAR	0	40	","	5	address
6	SQLCHAR	0	20	","	6	city
7	SQLCHAR	0	2	","	7	state
8	SQLCHAR	0	5	","	8	zip
9	SQLCHAR	0	1	"\r\n"	9	contract

The format file contains all the information necessary to bulk copy data from the data file to the Microsoft SQL Server table. However, the format file needs to be further modified with a text editor to change the server column order (sixth field in the format file) of the au_lname and au_fname fields.

7.0 9

1	SQLCHAR	0	11	","	1	au_id
2	SQLCHAR	0	40	","	3	au_lname
3	SQLCHAR	0	20	","	2	au_fname
4	SQLCHAR	0	12	","	4	phone
5	SQLCHAR	0	40	","	5	address
6	SQLCHAR	0	20	","	6	city
7	SQLCHAR	0	2	","	7	state
8	SQLCHAR	0	5	","	8	zip
9	SQLCHAR	0	1	"\r\n"	9	contract

The data in the data file can now be bulk copied into authors2 using the command:

bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\new_auth.dat'  WITH (FORMATFILE = 'c:\authors.fmt')

Copying Data from a Data File to SQL Server

To bulk copy a data file to Microsoft SQL Server, follow these guidelines:

When bulk copying data to a table with no indexes, set the select into/bulkcopy database option to true.

This is recommended to help prevent the transaction log from running out of space because row inserts are not logged. The system administrator or database owner can set this option with SQL Server Enterprise Manager (or the sp_dboption system stored procedure). For more information, see "Logged and Nonlogged Bulk Copy Operations" in this volume.
If you are loading a large amount of data relative to the amount of data already in the table, it can be quicker to drop the indexes on the table before performing the bulk copy operation.

Conversely, if you are loading a small amount of data relative to the amount of data already in the table, dropping the indexes may not be necessary because the time taken to rebuild the indexes can be longer than performing the bulk copy operation. For more information, see "Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics.
Be sure that the user account used to log in to SQL Server using bcp (or the query tool when using the BULK INSERT statement) has SELECT and INSERT permissions on the table (assigned by the table owner).

Note Only members of the sysadmin fixed server role can execute the BULK INSERT statement.
If the bulk copy operation was not logged, (select into/bulkcopy was set to true), back up the database by creating either a full or differential database backup after bulk copying data from a data file. This ensures that the bulk copy and any subsequent changes can be recovered in the event of a system failure. For more information, see "Backing Up Nonlogged Operations" in this volume.

Cc917564.fig5_2(en-us,TechNet.10).gif

To bulk copy data successfully into a table from a data file with the bcp utility or BULK INSERT statement, the terminators in the data file must be known, and specified.

The Newpubs.dat file:

1111,Stone Age Books,Boston,MA,USA 2222 ,Harley & Davidson,Washington,DC,USA 3333 ,Infodata Algosystems,Berkeley,CA,USA

Because the data file is all character data, the following options and parameters need to be specified.

Bulk copy option	bcp utility parameter	BULK INSERT clause
Character mode format	-c	DATAFILETYPE = 'char'
Field terminator	-t	FIELDTERMINATOR
Row terminator	-r	ROWTERMINATOR

In the Newpubs.dat file, each field in a row ends with a comma (,); each row ends with a newline character (\n).

The publishers2 table in the following example can be created by executing:

USE pubs GO sp_dboption pubs, 'select into/bulkcopy', 'true' GO SELECT * INTO publishers2 FROM publishers GO

The command to bulk copy data from Newpubs.dat into publishers2 is:

bcp pubs..publishers2 in newpubs.dat -c -t , -r \n -Sservername -Usa -Ppassword

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

Data from the Newpubs.dat file has been now appended to publishers2:

pub_id	pub_name	city	state	country
------	----------------	----------	-----	-----
0736	New Moon Books	Boston	MA	USA
0877	Binnet & Hardley	Washington	DC	USA
1111	Stone Age Books	Boston	MA	USA
1389	Algodata Infosystems	Berkeley	CA	USA
1622	Five Lakes Publishing	Chicago	IL	USA
1756	Ramona Publishers	Dallas	TX	USA
2222	Harley & Davidson	Washington	DC	USA
3333	Infodata Algosystems	Berkeley	CA	USA
9901	GGG&G	München		Germany
9952	Scootney Books	New York	NY	USA
9999	Lucerne Publishing	Paris		France

See Also

In This Volume

Creating and Restoring a Database Backup

In Other Volumes

"ImportData Method" in Microsoft SQL Server Distributed Management Objects

"UseBulkCopyOption Property" in Microsoft SQL Server Distributed Management Objects

"SuspendIndexing Property" in Microsoft SQL Server Distributed Management Objects

Transferring Data with bcp

To bulk copy data from one Microsoft SQL Server database to another, data from one SQL Server installation must be bulk copied to a data file, which is then bulk copied to the other SQL Server installation.

After bulk copying data into a table, it is a good idea to back up the database. Then, as necessary, re-create any indexes on the table, reset any database options, check to make sure that any triggers that need to run are executed, and make sure the select into/bulkcopy option is set to false.

Note Native, character, and Unicode format bcp can be used to bulk copy data between different computers running SQL Server on different processor architectures. However, the same format must be used when importing as exporting.

Storing information in Unicode native format is useful when information is to be copied from one computer running SQL Server to another. Using native format for noncharacter data saves time, preventing unnecessary conversion of data types to and from character format. Using Unicode character format for all character data prevents loss of any extended characters when bulk loading data between servers using different code pages (character loss is possible if extended characters are copied into non-Unicode columns if the extended character cannot be represented). However, a data file in Unicode native format cannot be read by any program other than bcp or the BULK INSERT statement.

It is also possible to copy data from one SQL Server database to another using:

The DTS Import and DTS Export wizards.
The Transact-SQL statements BACKUP and RESTORE (to copy entire databases).
Distributed queries as part of an INSERT statement.
The SELECT INTO statement.

See Also

In This Volume

Unicode Character Format bcp

In Other Volumes

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SELECT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Distributed Queries" in Microsoft SQL Server Database Developer's Companion

"RESTORE" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Data Transformation Services Import and Export Wizards" in Microsoft SQL Server Distributed Data Operations and Replication

"Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics

Copying Data From a Query to a Data File

The bcp utility allows you to copy the results set from a Transact-SQL statement to a data file. The Transact-SQL statement can be any valid statement that returns a results set, such as a distributed query or a SELECT statement joining several tables. For example, to copy the names of all the authors, ordered by surname, from the authors table in the pubs database to the Authors.txt data file, execute at the command prompt:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword

Bulk copying data from a query is useful if you want to ensure that the order of the data is preserved in the data file; bulk copying data from a table or view does not guarantee the order of the data written to the data file. Preserving the order of the data in the data file allows you to make use of the ORDER hint when bulk copying data from the data file back into a table. Using the ORDER hint can significantly improve bulk copy performance. For more information, see "Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics.

If the Transact-SQL statement returns multiple result sets, such as a SELECT statement that specifies the COMPUTE clause, or the execution of a stored procedure that contains multiple SELECT statements, only the first result set is copied; subsequent result sets are ignored.

See Also

In This Volume

Ordered Data Files

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

Copying To or From a Temporary Table

When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:

bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -Ppassword

However, do not specify the database name when using global temporary tables because temporary tables only exist in tempdb. It is only possible to use a local temporary table (for example, #temp_authors) when bulk copying data using the BULK INSERT statement.

Copying To or From a View

Data can be bulk copied to or from a view. This includes copying data from multiple joined tables, adding a WHERE clause, or performing special formatting such as changing data formats using the CONVERT function. For example, to bulk copy data from the view titleview in the pubs database to the Titleview.txt data file, execute at the command prompt:

bcp pubs..titleview out titleview.txt -c -Sservername -Usa -Ppassword

To bulk copy data into a view using bcp or the BULK INSERT statement, the rules for inserting data into a view apply.

See Also

In Other Volumes

"Modifying Data Through a View" in Microsoft SQL Server Database Developer's Companion

Factors Affecting Bulk Copy Performance

To bulk copy data as fast as possible, it is important to understand how data is copied, and what options are available to specify how data should be copied.

Bulk copy using the query processor
Logged and nonlogged bulk copies
Parallel data load using bcp
Controlling the locking behavior
Using batches
Constraint checking
Ignoring DEFAULT definitions
Ordered data files

See Also

In Other Volumes

"Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics

Bulk Copy Using the Query Processor

The bcp utility works in conjunction with the query processor to insert data into Microsoft SQL Server. The bcp utility generates client OLE DB rowsets that are sent to SQL Server and are inserted into the table by the query processor. This has the advantage of allowing the query processor to plan and optimize queries that import and export data from SQL Server. It also allows optimized index maintenance, constraint checking, and parallel data load operations. The BULK INSERT statement works in conjunction with the query processor to bulk copy data into SQL Server.

Any program written using the bulk copy API takes advantage of using client OLE DB rowsets and the SQL Server query processor to insert data.

See Also

In This Volume

Constraint Checking

Parallel Data Load Using bcp

In Other Volumes

"Bulk-Copy Rowsets" in Microsoft SQL Server Building Applications

"Query Processor Architecture" in Microsoft SQL Server Introduction

Logged and Nonlogged Bulk Copy Operations

By default, all row-insert operations performed by bcp are logged in the transaction log. For large data loads, this can cause the transaction log to fill rapidly. To help prevent the transaction log from running out of space, a nonlogged bulk copy can be performed if all of these conditions are met:

The database option select into/bulkcopy is set to true using sp_dboption.
The target table is not being replicated.
The TABLOCK hint is specified. For more information, see "Controlling the Locking Behavior" in this volume.

Additionally, if the table has any indexes, then there should be no existing rows in the table to perform a nonlogged bulk copy. The combination of indexes and existing rows of data cause the bulk copy operation to be logged.

Any bulk copy into Microsoft SQL Server that does not meet these conditions is logged.

Important Before a user can do nonlogged bulk copies, the system administrator or database owner must first use SQL Server Enterprise Manager (or the sp_dboption system stored procedure) to set the select into/bulkcopy option for that database to true. If the option is not set and a user tries to bulk copy data into a table that does not have indexes, SQL Server generates a warning message and logs the bulk copies. By default, select into/bulkcopy is false in newly created databases. To change the default for all new databases, set this option in the model database.

After performing a nonlogged bulk copy, it is no longer possible to back up the transaction log. Therefore, it is recommended that a database or differential database backup is created instead. For more information, see "Backing Up Nonlogged Operations" in this volume.

While minimal logging occurs when bulk copying data into a table with indexes, the transaction log can still become full. The log can be truncated using the Transact-SQL statement BACKUP LOG specifying the TRUNCATE_ONLY clause.

When bulk copying a large number of rows into a table with indexes, it can be faster to drop all the indexes, perform the bulk copy, and re-create the indexes. For more information, see "Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics.

Note Although data insertions are not logged in the transaction log when a nonlogged bulk copy is performed, SQL Server still logs extent allocations each time a new extent is allocated to the table.

See Also

In Other Volumes

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Nonlogged Operations" in Microsoft SQL Server Database Developer's Companion

"UseBulkCopyOption Property" in Microsoft SQL Server Distributed Management Objects

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SuspendingIndexing Property" in Microsoft SQL Server Distributed Management Objects

Parallel Data Load Using bcp

Microsoft SQL Server allows data to be bulk copied into a single table from multiple clients in parallel using the bcp utility or BULK INSERT statement. This can improve the performance of data load operations. To bulk copy data into SQL Server in parallel:

Set the database option select into/bulkcopy to true using sp_dboption.
Specify the TABLOCK hint. For more information, see "Controlling the Locking Behavior" in this volume.
Ensure that the table does not have any indexes.

Note Any application based on the DB-Library client library supplied with SQL Server version 6.5 or earlier, including the bcp utility, is not able to participate in parallel data loads into SQL Server. Only applications using the ODBC or SQL OLE DB-based APIs can perform parallel data loads into a single table.

After data has been bulk copied into a single table from multiple clients, any nonclustered indexes that need to be created can also be created in parallel by simply creating each nonclustered index from a different client concurrently.

Note Any clustered index on the table should be created first from a single client before creating the nonclustered indexes.

See Also

In This Volume

Logged and Nonlogged Bulk Copy Operations

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics

Controlling the Locking Behavior

The bcp utility and BULK INSERT statement accept the TABLOCK hint that allows the user to specify the locking behavior used. TABLOCK specifies that a bulk update (BU) table-level lock is taken for the duration of the bulk copy. Using TABLOCK can improve performance of the bulk copy operation due to reduced lock contention on the table. For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying a table-level lock, execute from the command prompt:

bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "TABLOCK"

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\authors.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', TABLOCK )

If TABLOCK is not specified, the default is to use row-level locks, unless the table lock on bulk load option is set to on. Setting the table lock on bulk load option using sp_tableoption sets the locking behavior for a table during a bulk load.

Table lock on bulk load	Table locking behavior
off	Row-level locks used
on	Table-level lock used

If the TABLOCK hint is specified, the default setting for the table set with sp_tableoption is overridden for the duration of the bulk load.

Note It is not necessary to use the TABLOCK hint to bulk load data into a table from multiple clients in parallel, although doing so can improve performance.

See Also

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_tableoption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Understanding Locking in SQL Server" in Microsoft SQL Server Database Developer's Companion

Using Batches

The bcp utility and BULK INSERT statement accept two parameters that allow the user to specify the number of rows per batch sent to Microsoft SQL Server for the bulk copy operation.

bcp utility parameter	BULK INSERT clause
-b batch_size	BATCHSIZE = batch_size
-h "ROWS_PER_BATCH = bb"	ROWS_PER_BATCH = rows_per_batch

The use of these parameters has a large effect on how data insertions are logged.

Using the -b Parameter or BATCHSIZE Clause

Each batch of rows is inserted as a separate transaction. If, for any reason, the bulk copy operation terminates before completion, only the current transaction is rolled back. For example, if a data file has 1,000 rows, and a batch size of 100 is used, SQL Server logs the operation as 10 separate transactions; each transaction inserts 100 rows into the destination table. If the bulk copy operation terminates while copying row 750, only the previous 49 rows are removed as SQL Server rolls back the current transaction. The destination table still contains the first 700 rows. If -b or BATCHSIZE are not specified, the entire file is sent to SQL Server and the bulk copy operation is treated as a single transaction.

Using ROWS_PER_BATCH

If the -b parameter or BATCHSIZE clause is not used, the entire file is sent to SQL Server and the bulk copy operation is treated as a single transaction. In this case, the ROWS_PER_BATCH hint or ROWS_PER_BATCH clause can be used to give an estimate of the number of rows. SQL Server optimizes the load automatically according to the batch size value, which may result in better performance.

Note Generally, the larger the batch size is, the better the performance of the bulk copy operation will be. Make the batch size as large as is practical, although accuracy in the hint is not critical.

If, for any reason, the operation terminates before completion, the entire transaction is rolled back, and no new rows are added to the destination table.

Although all rows from the data file are copied into SQL Server in one batch, bcp displays the message "1000 rows sent to SQL Server" after every 1000 rows. This message is for information only and occurs regardless of the batch size used.

Note Supplying both parameters with different batch sizes will generate an error message.

When bulk copying large data files into SQL Server, it is possible for the transaction log to fill before the bulk copy is complete, even if the row inserts are not logged, from the extent allocation logging. In this situation, enlarge the transaction log, allow it to grow automatically, or perform the bulk copy using the -b or BATCHSIZE parameter, and set the database option trunc. log on chkpt. to true using sp_dboption or SQL Server Enterprise Manager. Setting this option instructs SQL Server to truncate the transaction log each time it performs a CHECKPOINT, removing the log records for committed batches from the transaction log. Because only committed transactions can be truncated, this option does not free up space during the bulk copy operation if the -b parameter is not used because the entire operation is logged as a single transaction.

The bcp utility and BULK INSERT statement also accept the KILOBYTES_PER_BATCH hint or KILOBYTES_PER_BATCH clause respectively that can be used to specify the approximate amount of data (in kilobytes) contained in a batch. SQL Server optimizes the bulk load according to the value set.

Batch sizes are not applicable when bulk copying data from SQL Server to a data file.

See Also

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"sp_dboption" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ImportRowsPerBatch Property" in Microsoft SQL Server Distributed Management Objects

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics

Constraint Checking

The bcp utility and BULK INSERT statement accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause respectively, which allows the user to specify whether constraints are checked during a bulk load.

By default, constraints are ignored during the bulk load. This improves the performance of the bulk load, but allows the possibility of data being inserted into the table that violates existing constraints. CHECK_CONSTRAINTS specifies that constraints are enforced during the bulk load. This reduces the performance of the bulk load, but ensures that all data inserted does not violate any existing constraints. For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying that any constraints should be enforced, execute from the command prompt:

bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "CHECK_CONSTRAINTS"

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\authors.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', CHECK_CONSTRAINTS )

When data is copied into a table, any triggers defined for the table are ignored.

To find any rows that violate constraints or triggers, it is necessary to check the copied data manually using queries. Bulk copy data into the table, and run queries or stored procedures that test the constraint or trigger conditions, such as:

UPDATE pubs..authors2 SET au_fname = au_fname

Although this query does not change data to a different value, it causes Microsoft SQL Server to update each value in the au_fname column to itself. This causes any constraints or triggers to be tested.

Note Although, by default, constraints on the table are not checked for the bulk copy operation unless CHECK_CONSTRAINTS is specified, constraints act as expected for other concurrent operations, such as INSERT, UPDATE, or DELETE.

See Also

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Ignoring DEFAULT Definitions

The bcp utility accepts the -k parameter, and the BULK INSERT statement accepts the KEEPNULLS clause, which can be used to specify that empty columns should retain a null value during the bulk copy operation, rather than have any default values for the columns inserted.

Note If default values are not inserted, the column must be defined to allow null values.

By default, when data is copied into a table using the bcp utility or BULK INSERT statement, any defaults defined for the columns in the table are observed. For example, if there is a null field in a data file, the default value for the column is loaded instead.

For example, the data file Publishers.txt has two rows:

0111,New Moon Books,Boston,MA, 0222,Binnet & Hardley,Washington,DC,USA

Commas separate the fields; a newline character separates the rows. There is no country for the first row. If the country column of the publishers table had a default of "USA", the rows bulk loaded into the table by bcp or the BULK INSERT statement when the -k parameter or KEEPNULLS clause is not specified are:

0111 New Moon Books Boston MA USA 0222 Binnet & Hardley Washington DC USA

Alternatively, to bulk copy data from the Publishers.txt data file into the publishers table in the pubs database and insert the value null into the country column, rather than the default value of "USA", execute from the command prompt:

bcp pubs..publishers in publishers.txt -c -t, -Sservername -Usa -Ppassword -k

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..publishers FROM 'c:\publishers.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', KEEPNULLS )

Note Although DEFAULT definitions on the table are not checked for the bulk copy operation if -k or KEEPNULLS is specified, DEFAULT definitions are expected for other concurrent INSERT statements.

See Also

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Creating and Modifying DEFAULT Definitions" in Microsoft SQL Server Database Developer's Companion

"BACKUP" in Microsoft SQL Server Transact-SQL and Utilities Reference

"ServerBCPKeepNulls Property" in Microsoft SQL Server Distributed Management Objects

Ordered Data Files

The bcp utility and BULK INSERT statement accept the ORDER hint and ORDER clause respectively, which allows the user to specify how data in the data file is sorted. Although it is not necessary for data in the data file to be sorted in the same order as the table, the same ordering can improve performance of the bulk copy operation.

The order of data in the table is determined by the clustered index. The order and columns listed in the ORDER hint or ORDER clause must match the columns, in the same order, in the clustered index to improve the performance of the bulk copy operation.

For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying that the data file is in ascending order on the au_id column, execute from the command prompt:

bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "ORDER (au_id ASC)"

Alternatively, using the BULK INSERT statement from a query tool such as SQL Server Query Analyzer to bulk copy data:

BULK INSERT pubs..authors2 FROM 'c:\authors.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ORDER (au_id ASC) )

By default, the bulk copy operation assumes that the data file is unordered.

See Also

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"Optimizing Bulk Copy Performance" in Microsoft SQL Server Diagnostics

Common bcp Issues

Common issues that arise when bulk copying data include:

Copying data between different code pages
Using delimited identifiers with bcp
Loading ASCII files
Native format bcp and improperly defined tables
Finding hidden characters in data files
Using default data type sizes
Copying date and money values
Importing image data
Copying data containing identity values

Copying Data Between Different Code Pages

When bulk copying data using native or character format, bcp, by default, converts character data to:

OEM code page characters when exporting data from Microsoft SQL Server.
ANSI/Microsoft Windows code page characters when importing data into SQL Server.

This can cause the loss of extended or DBCS characters during the conversion between OEM and ANSI code pages. To prevent the loss of extended or DBCS characters, bcp can create data files using:

Unicode native data format (-N).
Unicode character data format (-w).
A specific code page (-C).

Unicode native format and Unicode character format convert character data to Unicode during the bulk copy, resulting in no loss of extended characters.

Using the -C (code page) parameter, the bcp utility can create or read data files using the code page specified by the user. For example, to bulk copy the authors2 table in the pubs database to the Authors.txt data file using code page 850, execute from the command prompt:

bcp pubs..authors2 out authors.txt -c -C850 -Sservername -Usa -Ppassword

Alternatively, using the CODEPAGE clause, the BULK INSERT statement can read data files using the code page specified by the user. For example, to bulk copy the Authors.txt data file into the authors2 table in the pubs database using code page 850, execute from a query tool such as SQL Server Query Analyzer:

BULK INSERT pubs..authors2 FROM 'c:\authors.txt' WITH ( CODEPAGE = 850 )

Valid values for the code page include the following.

Code page value	Description
ACP	Columns of char, varchar, or text data type are converted from the ANSI/Windows code page (ISO 1252) to the SQL Server code page when importing data to SQL Server, and vice versa when exporting data from SQL Server.
OEM (default)	Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page when importing data to SQL Server, and vice versa when exporting data from SQL Server.
RAW	This is the fastest option because no conversion from one code page to another occurs.
<value>	Specific code page number, for example, 850.

See Also

In This Volume

Unicode Native Format bcp

Unicode Character Format bcp

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"SetCodePage Method" in Microsoft SQL Server Distributed Management Objects

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Using Delimited Identifiers with bcp

When referencing object names containing embedded special characters, such as spaces in a table name, the object name needs to be delimited with either double quotation marks (" ") or square brackets ([ ]), and the -q parameter must be specified (when using the bcp utility). For example, to bulk copy the Order Details table in the Northwind database to the Orders.txt data file, execute at the command prompt:

bcp "Northwind..""Order Details""" out orders.txt -c -q -Sservername -Usa -Ppassword

bcp "Northwind..[Order Details]" out orders.txt -c -q -Sservername -Usa -Ppassword

bcp "Northwind..Order Details" out orders.txt -c -q -Sservername -Usa -Ppassword

Using the -q parameter causes the connection to Microsoft SQL Server used by the bcp utility to have the SET QUOTED_IDENTIFIER ON statement executed. The outer quotation marks around the entire three-part object name are required because the command prompt requires any command parameter containing a blank or double quotation mark to be surrounded with double quotation marks, and any embedded double quotation mark characters (first example) to be surrounded with double quotation marks themselves.

Using the BULK INSERT statement, delimit the object name with square brackets. For example, to bulk copy the Orders.txt data file into the Order Details table in the Northwind database, execute from a query tool such as SQL Server Query Analyzer:

BULK INSERT Northwind..[Order Details] FROM 'c:\orders.txt' WITH ( DATAFILETYPE = 'char' )

See Also

In Other Volumes

"Delimited Identifiers" in Microsoft SQL Server Database Developer's Companion

"SET QUOTED_IDENTIFIER" in Microsoft SQL Server Transact-SQL and Utilities Reference

Loading ASCII Files

Often, users attempt to load an ASCII file in the Microsoft SQL Server native format. This leads to misinterpretation of the hexadecimal values in the ASCII file and sometimes the "unexpected end of file" error message. The correct method of loading the ASCII file is to represent each field in the data file as a character string (character format bcp) and let SQL Server do the data conversion to internal data types (for example, int, float, or datetime) as rows are inserted into the table.

Native Format bcp and Improperly Defined Tables

Using native format to bulk copy data into an improperly defined table can cause the table to be loaded incorrectly. The incorrect loading may appear as an unusual formatting of data in the target table. This also applies to client tools that use the bcp API in native mode.

Native format is intended for high-speed data transfer between identically defined Microsoft SQL Server tables. To achieve the optimum transfer rate, few checks are performed regarding data formatting. If the table is not defined correctly, use character format.

Correct table definition includes the correct number of columns, data type, length, and NULL status.

Finding Hidden Characters in Data Files

A hidden character in an ASCII data file can cause problems when trying to bulk copy data into Microsoft SQL Server, resulting in an "unexpected null found" error message. Many utilities and text editors display hidden characters which can usually be found at the bottom of the data file. Finding and removing these characters should resolve the problem.

Using Default Data Type Sizes

Using default data type sizes (field length) can lead to an "unexpected end of file" error message. This generally occurs with the money and datetime data types when only part of the field occurs in the data file (for example, a datetime value of mm/dd/yy with no time component) rather than an entire string, as expected by Microsoft SQL Server. When using the default size option, SQL Server expects to read 24 characters (the length of the datetime data type when stored in char format).

To avoid this problem, bulk copy data using field terminators, or fixed-length data fields.

See Also

In This Volume

Field Length

Copying Date and Money Values

The bcp utility (which uses the ODBC bulk copy API, rather than the DB-Library bulk copy API) imports character format data files containing date values using ODBC format (yyyy-mm-dd hh:mm:ss[.f...]). To bulk copy date formats supported by earlier versions of the bcp utility, but no longer supported by ODBC, into Microsoft SQL Server, use the -6 parameter. When using the -6 parameter, the bcp utility first attempts to convert the date value in the data file using ODBC date format. If the conversion fails, bcp attempts to convert the date value using DB-Library formats.

Note Using the -6 flag can affect performance because of the overhead of supporting multiple date conversions, and only works with the native (-n) and character (-c) formats (not Unicode).

The bcp utility always exports character format data files using the ODBC default format for datetime and smalldatetime values even if -6 is specified. For example, a datetime column containing the date 12 Aug 1998, is bulk copied to a data file as the character string 1998-08-12 00:00:00.000.

The BULK INSERT statement does allow any date format supported by SQL Server to be used when importing data into SQL Server.

The bcp utility exports money values in character format data files without digit grouping symbols such as comma separators, but with four digits after the decimal point. For example, a money column containing the value 1,234,567.123456 is bulk copied to a data file as the character string 1234567.1235. To import files created by an earlier version of bcp (that used the DB-Library API), specify the -6 parameter.

To bulk copy money, datetime, or smalltime values into SQL Server using the regional format defined for the locale setting of the client computer, specify the -R parameter.

See Also

In This Volume

Modifying Native and Character Format bcp Using -6

In Other Volumes

"Backward Compatibility Details (Level 2)" in Microsoft SQL Server Introduction

"Use6xCompatible Property" in Microsoft SQL Server Distributed Management Objects

"CAST and CONVERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

Importing image Data

It is possible to bulk copy a data file as image data into Microsoft SQL Server. The command to load the data file Test.doc into the bitmap table in the pubs database using the bcp utility is:

bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservername

bcp prompts:

Enter the file storage type of field c1 [image]: Enter the prefix length of field c1 [4]: 0 Enter length of field c1 [4096]: 5578  Enter the field terminator [none]:

In this example, the data file will be loaded into column c1 and 5578 is the length of the data file.

Using the BULK INSERT statement, a format file needs to be created first and then used to provide the format information. To create the format file, use the bcp utility:

bcp pubs..bitmap out c:\bitmap.txt -Sservername -Usa -Ppassword

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of bitmap. For the c1 column, the values are listed in this table.

Prompt	Value
File storage type	image
Prefix length	0
Field length	5578
Field terminator	none

The Bcp.fmt file:

7.0 1 1 SQLBINARY 0 5578 "" 1 c1

Using the BULK INSERT statement to bulk copy the Test.doc data file into the bitmap table in the pubs database, execute from a query tool such as SQL Server Query Analyzer:

BULK INSERT pubs..bitmap FROM 'c:\test.doc' WITH ( FORMATFILE = 'c:\Bcp.fmt' )

Note It is not possible to bulk copy data into text, ntext, and image columns that have DEFAULT values.

Copying Data Containing Identity Values

The bcp utility and BULK INSERT statement allow data files containing identity values to be bulk copied into Microsoft SQL Server. The bcp utility accepts the -E parameter, and the BULK INSERT statement accepts the KEEPIDENTITY clause, that can be used to prevent SQL Server from supplying identity values. While the rows in the data file are bulk copied into the table, SQL Server does not assign unique identity values automatically; the identity values are taken from the data file.

If these options are not supplied, the values for the identifier column in the data file being imported are ignored and SQL Server assigns unique values automatically based on the seed and increment values specified during table creation. If the data file does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data; SQL Server assigns unique values automatically for the column.

See Also

In This Volume

Using a Data File with Fewer Fields

In Other Volumes

"bcp Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference

"BULK INSERT" in Microsoft SQL Server Transact-SQL and Utilities Reference

"IncludeIdentityValues Property" in Microsoft SQL Server Distributed Management Objects

"ServerBCPKeepIdentity Property" in Microsoft SQL Server Distributed Management Objects

Tips and Notes for Using the bcp Utility

It is possible to specify the number of rows to load from the data file rather than loading the entire data file. For example, to load only the first 150 rows from a 10,000 row data file, specify the -L last_row parameter when loading the data. This can be useful for testing a batch load process.
When using the -F first_row parameter to specify the first row in the table or view to start bulk copying data from Microsoft SQL Server to a data file, all rows in the table or view are first returned to the client, and then the bcp utility determines which rows to skip and write to the data file. Therefore, specifying -F first_row does not limit the amount of data returned to the client and does not cause the bulk copy operation to necessarily execute any faster.
Because SQL Server can use parallel scans to retrieve data, the data bulk copied from SQL Server is not guaranteed to be in any specific order unless you bulk copy data from a query and specify an ORDER BY clause.
To copy data from earlier versions of SQL Server using native format data files, use the same version of bcp for importing, exporting, and formatting files.

See Also

In Other Volumes

"LastRow Property" in Microsoft SQL Server Distributed Management Objects

Chapter 2 - Importing and Exporting Data

Choosing a Tool to Import or Export Data

Copying Data Using the Data Transformation Services Wizards

Copying Data Using bcp or BULK INSERT

Running the bcp Utility

Native, Character, and Unicode Formats

Native Format bcp

Character Format bcp

Modifying Native and Character Format bcp Using -6

Unicode Character Format bcp

Unicode Native Format bcp

Interactive bcp

File Storage Type

Prefix Length

Field Length

Field Terminator

Using the bcp Format File

Using a Data File with Fewer Fields

Using a Data File with More Fields

Using a Data File with Fields in a Different Order

Copying Data from a Data File to SQL Server

Transferring Data with bcp

Copying Data From a Query to a Data File

Copying To or From a Temporary Table

Copying To or From a View

Factors Affecting Bulk Copy Performance

Bulk Copy Using the Query Processor

Logged and Nonlogged Bulk Copy Operations

Parallel Data Load Using bcp

Controlling the Locking Behavior

Using Batches

Constraint Checking

Ignoring DEFAULT Definitions

Ordered Data Files

Common bcp Issues

Copying Data Between Different Code Pages

Using Delimited Identifiers with bcp

Loading ASCII Files

Native Format bcp and Improperly Defined Tables

Finding Hidden Characters in Data Files

Using Default Data Type Sizes

Copying Date and Money Values

Importing image Data

Copying Data Containing Identity Values

Tips and Notes for Using the bcp Utility

Additional resources