| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | monikerRange | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Import native & character format data from earlier versions of SQL Server |
In SQL Server 2019, you can use bcp to import native and character format data from other versions of SQL Server by using the -V switch with a qualifier. |
rwestMSFT |
randolphwest |
03/14/2017 |
sql |
data-movement |
conceptual |
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]
In [!INCLUDE sssql14-md] and later, you can use bcp to import native and character format data from [!INCLUDEssVersion2000], [!INCLUDEssVersion2005], [!INCLUDEsql2008-md], [!INCLUDEsql2008r2], or [!INCLUDEssSQL11] by using the -V switch. The -V switch causes [!INCLUDEssnoversion] to use data types from the specified earlier version of [!INCLUDEssNoVersion], and the data file format are the same as the format in that earlier version.
To specify an earlier [!INCLUDEssNoVersion] version for a data file, use the -V switch with one of the following qualifiers:
| SQL Server version | Qualifier |
|---|---|
| [!INCLUDEssVersion2000] | -V80 |
| [!INCLUDEssVersion2005] | -V90 |
| [!INCLUDEsql2008-md] | -V100 |
| [!INCLUDEssSQL11] | -V 110 |
[!INCLUDEssVersion2005] and later versions have support for some new types. When you want to import a new data type into an earlier [!INCLUDEssNoVersion] 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 are converted for compatibility with the earlier versions of [!INCLUDEssNoVersion].
| 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 |
| UDT** | image | image | image |
*This type is natively supported.
**UDT indicates a user defined type.
When you bulk export data by using the -V80 switch, 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 [!INCLUDEssVersion2005] and later versions.
bcp uses the ODBC bulk copy API. Therefore, to import date values into [!INCLUDEssNoVersion], 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
-
Use Unicode Character Format to Import or Export Data (SQL Server)
-
Use Unicode Native Format to Import or Export Data (SQL Server)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Data Types (Transact-SQL)
SQL Server Database Engine Backward Compatibility
CAST and CONVERT (Transact-SQL)