Import native format data and character format data from earlier versions of SQL Server

Import SQL Server from an earlier version native format data and character format data

In SQL Server 2014, you can use BCP with the-v switch, from SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server R2, or SQL Serv The native and character format data is imported in ER 2012. The-v switch causes SQL Server 2014 to use the data types in earlier versions of SQL Server that you specified, and the data file format is the same as in earlier versions.

To specify a previous version of SQL Server for a data file, you can use the-v switch with any of the following qualifiers:

SQL Server version	Qualifier
SQL Server 2000	- v the
SQL Server 2005	- v -
SQL Server 2008	- v -
SQL Server 2012	-V 110

Explanation of the data type

Some new types are supported in SQL Server 2005 and later versions. If you are importing a new data type into a previous version of SQL Server, you must store the data type in a format that is readable by the earlier bcp client. The following table summarizes how to convert the new data types to be compatible with earlier versions of SQL Server.

SQL Server 2005 the new data type in	Version 6x compatible data types	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 supported by this machine.

A UDT represents a user-defined type.

Use –V to export

When you bulk export data using the –V80 switch, the nvarchar (max), varchar (max), varbinary (max), XML, and UDT data in native mode are stored as a 4-byte prefix as text, image, and ntext data , instead of being stored as a 8-byte prefix, as is the default for SQL Server 2005 and later versions.

Copy Date value

BCP will use the ODBC bulk copy API. Therefore, in order to import date values into SQL Server, BCP uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f ...]).

For character-formatted data files, the BCP command exports the file using the ODBC default format for values of datetime and smalldatetime types. For example, a datetime column containing a date of 1998 will be bulk copied to the data file in the form of string 1998-08-12 00:00:00.000.

Important Notes

When using bcp to import data into the smalldatetime field, make sure that the seconds value is 00.000 or the operation will fail. The smalldatetime data type only supports the nearest minute value. BULK Insert and insert ... SELECT * from OPENROWSET (BULK ...) does not fail in this case, but the seconds value is truncated.

Reference:

Https://msdn.microsoft.com/zh-cn/library/ms191212.aspx

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1628106

Import native format data and character format data from earlier versions of SQL Server