Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
In SQL Server 2014 (12.x) and later, you can use bcp to import native and character format data from SQL Server 2000 (8.x), SQL Server 2005 (9.x), SQL Server 2008 (10.0.x), SQL Server 2008 R2 (10.50.x), or SQL Server 2012 (11.x) by using the -V switch. The -V switch causes SQL Server 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 (8.x) | -V80 |
SQL Server 2005 (9.x) | -V90 |
SQL Server 2008 (10.0.x) | -V100 |
SQL Server 2012 (11.x) | -V 110 |
SQL Server 2005 (9.x) 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 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 |
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 SQL Server 2005 (9.x) and later versions.
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
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)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Migrate SQL Server workloads to Azure Virtual Machine - Training
Discover the tools and features available to migrate SQL workloads from on-premises to Azure Virtual Machines (VMs), including the Azure SQL Migration extension for Azure Data Studio and Data Migration Assistant.
Documentation
Use Unicode Native Format to Import or Export Data (SQL Server) - SQL Server
Use Unicode native format for bulk transfer of data between instances of SQL Server, which eliminates conversion of data types to and from character format.
Use Character Format to Import & Export Data - SQL Server
Character format uses character data format for all columns. This is useful working with other programs or copying to an instance from another database vendor.
Use native format to import & export data - SQL Server
In SQL Server import or export, native format maintains the native data types of a database for high-speed data transfer of data between SQL Server tables.