Study note about bulk import and export data in MSSQL

An XML native Format file

This format file was created by using the following bcp command.

cmd>bcp myTeam format nul -f myTeam.Xml -x -n -T -d <span style="font-family:'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif;">AdventureWorks</span>

Tips: You can use -T option instead of -U username and -P password

Command	Option	Description
bcp	-n	Causes the bcp utility to use the native data types of the data.1
BULK INSERT	DATAFILETYPE= 'native'	Uses the native or wide native data types of the data. Note that DATAFILETYPE is not needed if a format file specifies the data types.

-n	Specifies native data types.
-T	Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. If -T is not specified, you need to specify -U and -P to successfully log in.

Important !!!

At this case ( use native format , extended character data can be lost. For extended characters, either use Unicode native format or specify a code page.

how the bcp utility exports and imports data in native format.

Export

cmd>bcp Currency out Currency.dat -T -f Currency.xml -d AdventureWorks2012

Import

cmd>bcp Currency in Currency.dat -T -f Currency.xml -d AdventureWorks2012 -b 1000 -a 4096

Tips:

Using a format file in with the in or out option is optional. In the absence of the -foption, if -n, -c, -w, or -N is not specified, the command prompts for format information and lets you save your responses in a format file (whose default file name is Bcp.fmt).  也就是说如果不是 in/out 的情况，-f 就是用于保存format file，如果是 in/out 时，-f 就是指定你的format file.

-a  packet_size

Specifies the number of bytes, pernetworkpacket, sent to and from the server. A server configuration option can be set by using SQL Server Management Studio (or thesp_configure system stored procedure). However, the server configuration option can be overridden on an individual basis by using this option. packet_size can be from 4096 to 65535 bytes; the default is 4096.

Increased packet size can enhance performance of bulk-copy operations. If a larger packet is requested but cannot be granted, the default is used. The performance statistics generated by the bcp utility show the packet size used.

-b  batch_size

Specifies the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all the rows in the data file are imported as one batch. To distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. If the transaction for any batch fails, only insertions from the current batch are rolled back. Batches already imported by committed transactions are unaffected by a later failure.

Do not use this option in conjunction with the -h"ROWS_PER_BATCH = bb" option.