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

Source: Internet
Author: User
Tags bulk insert

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.