import-native-and-character-format-data-from-earlier-versions-of-sql-server.md

title	Import Native and Character Format Data from Earlier Versions of SQL Server | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.suite
ms.technology	dbe-bulk-import-export
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	earlier versions [SQL Server], import and export data formats -V switch data formats [SQL Server], earlier versions previous versions [SQL Server], import and export data formats
ms.assetid	e644696f-9017-428e-a5b3-d445d1c630b3
caps.latest.revision	40
author	JennieHubbard
ms.author	jhubbard
manager	jhubbard

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

In [!INCLUDEssCurrent], you can use bcp to import native and character format data from [!INCLUDEssVersion2000], [!INCLUDEssVersion2005], [!INCLUDEssKatmai], [!INCLUDEssKilimanjaro], or [!INCLUDEssSQL11] by using the -V switch. The -V switch causes [!INCLUDEssCurrent] 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
[!INCLUDEssKatmai]	-V100
[!INCLUDEssSQL11]	-V 110

Interpretation of Data Types

[!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
UDT1	image	image	image

* This type is natively supported.

¹ UDT indicates a user defined type.

Exporting using –V 80

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.

Copying Date Values

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.

Related Tasks

To use data formats for bulk import or bulk export

• Use Character Format to Import or Export Data (SQL Server)

• Use Native Format to Import or Export Data (SQL Server)

• Use Unicode Character Format to Import or Export Data (SQL Server)

• Use Unicode Native Format to Import or Export Data (SQL Server)

See Also

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Data Types (Transact-SQL)
SQL Server Database Engine Backward Compatibility
CAST and CONVERT (Transact-SQL)