Importing Native and Character Format Data from Earlier Versions of SQL Server
To use bcp to import native and character format data from Microsoft SQL Server 2005 or earlier, use the -V switch. When the -V switch is specified, SQL Server 2008 R2 uses data types from earlier versions of Microsoft SQL Server, and the data file format will be the same as the one in that earlier version.
To specify whether the data file is at the level of an earlier version of SQL Server, use the -V switch as follows:
SQL Server version |
Qualifier |
|---|---|
SQL Server 7.0 |
-V70 |
SQL Server 2000 |
-V80 |
SQL Server 2005 |
-V90 |
Interpretation of SQL Server Data Types
SQL Server 2005 and later versions have support for some new types. When you want to import a new data type into an earlier version, the data type must be stored in a format that readable by the older bcp clients. The following table summarizes how the new data types of SQL Server 2005 are converted for compatibility with the earlier versions of SQL Server.
New data types in SQL Server 2005 |
Compatible data types in version 6x |
Compatible data types in version 70 |
Compatible data types in version 80 |
|---|---|---|---|
bigint |
decimal |
decimal |
* |
sql_variant |
text |
nvarchar(4000) |
* |
varchar(max) |
text |
text |
text |
nvarchar(max) |
ntext |
ntext |
ntext |
varbinary(max) |
image |
image |
image |
XML |
ntext |
ntext |
ntext |
UDT1 |
image |
image |
image |
* This type is natively supported.
1 UDT indicates a user defined type.
Exporting from SQL Server 2005 and Later Versions
When you bulk export data by using the –V80 switch from SQL Server 2005 or later versions, nvarchar(max), varchar(max), varbinary(max), XML, and UDT data in native mode are stored with a 4-byte prefix, like text, image, and ntext data, rather than with an 8-byte prefix, which is the default for SQL Server 2005 and later versions.
Exporting from SQL Server 7.0 or SQL Server 2000
When you bulk export data from SQL Server 7.0 or SQL Server 2000, consider the following:
In SQL Server 7.0 and SQL Server 2000, the value 0 represents a zero-length column.
The storage format for bigint data exported from SQL Server 7.0 depends on data format of the data file:
In a native mode or Unicode native-format data file, bigint data is stored as decimal(19,0).
In a character mode or Unicode character-format data file, bigint data is stored as a character or Unicode string of [-]digits, (for example, –25688904432).
Copying Date Values
Beginning with SQL Server 7.0, bcp uses the ODBC bulk copy API. Therefore, to import date values into SQL Server 7.0 or later, bcp uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]).
The bcp command exports character format data files using the ODBC default format for datetime and smalldatetime values. 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.
Important
When importing data into a smalldatetime field using bcp, be sure the value for seconds is 00.000; otherwise the operation will fail. The smalldatetime data type only holds values to the nearest minute. BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) will not fail in this instance but will truncate the seconds value.