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