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