Share via


Import Native and Character Format Data from Earlier Versions of SQL Server

In SQL Server 2012, you can use bcp to import native and character format data from SQL Server 2000, SQL Server 2005, or SQL Server 2008, by using the -V switch. The -V switch causes SQL Server 2012 to use data types from the specified earlier version of SQL Server, and the data file format are the same as the format in that earlier version.

To specify an earlier SQL Server version for a data file, use the -V switch with one of the following qualifiers:

SQL Server version

Qualifier

SQL Server 2000 

-V 80

SQL Server 2005

-V 90

SQL Server 2008

-V 100

Interpretation of Data Types That Were Introduced in SQL Server 2005

SQL Server 2005 and later versions have support for some new types. When you want to import a new data type into an earlier SQL Server 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 –V 80 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.

Copying Date Values

bcp uses the ODBC bulk copy API. Therefore, to import date values into SQL Server, 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.

To use data formats for bulk import or bulk export

Arrow icon used with Back to Top link [Top]

See Also

Reference

bcp Utility

BULK INSERT (Transact-SQL)

OPENROWSET (Transact-SQL)

Data Types (Transact-SQL)

CAST and CONVERT (Transact-SQL)

Other Resources

SQL Server Database Engine Backward Compatibility