Bulk import and export of data formats-character formats

Source: Internet
Author: User
Tags bulk insert

Bulk Import and export of data formats-- Character Format



Application Scenarios


Character formatting is recommended when you bulk export data to a text file that you want to use in another program , or when you bulk import data from a text file generated by another program.

With character formatting, all columns are applied with the characters data format. Storing information in character format is useful if you want to use data for other programs, such as spreadsheet programs, or if you need to copy data to an instance of SQL Server through another database vendor, such as Oracle.

Precautions


When you use character formatting, consider the following things:


1. By default, the bcp utility uses a tab-delimited character data field and terminates the record with a newline character.


2. By default, the following conversions are performed before bulk export or import of character-mode data:


Direction of the bulk operation

Transformation

Export

Converts the data to a character representation. If you make an explicit request, the data for the character column is converted to the requested code page. If no code page is specified, character data is converted by using the OEM code page of the client computer.

Import

Converts character data to a native representation, if necessary, and converts character data from the client's code page to the code page of the destination column.


3. To avoid losing extended characters during conversion, use Unicode character formatting or specify a code page.


4. All sql_variant data stored in a character format file is stored without the inclusion of metadata. Each data value is converted to the char format as an implicit data conversion rule. When the data is imported into the sql_variant column, the data is imported in char format. When imported into a column with a data type other than sql_variant, the data is converted from char format to another format by implicit conversions.


5. When the bcp utility exports a money value as a character format data file, the data file retains four digits after the decimal point and does not have a numeric grouping symbol such as a comma delimiter. For example, a money column that contains a value of 1,234,567.123456 will be bulk exported to a data file in the form of string 1234567.1235.


Command options


You can use bcp, BULK Insert, or INSERT ... SELECT * from OPENROWSET (BULK ...) to import character format data into a table. For bcp commands or BULK INSERT statements, you can specify the data format on the command line. For INSERT ... SELECT * from OPENROWSET (BULK ...) statement, you must specify the data format in the format file.

The following command-line options support character formatting:

Command

Options

Description

Bcp

-C

Enables the bcp utility to use character data.

BULK INSERT

DataFileType = 'char'

Character formatting is used when bulk importing data.


To load the character (-c) data into a format that is compatible with earlier versions of SQL Server client, use the-v switch.

Reference:

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

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

Bulk Import and export data formats-character formatting

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.