431. chapter10. working with flat files

 

Ii. Bulk Copy Program:
1 Definition: a command line tool that uses APIs for large-capacity replication. It can execute the following tasks
(1) export large data volumes from SQL Server to data files
(2) Export tree data in large capacity from queries
(3) import large data volumes to SQL Server
(4) generate a formatted File

2. Disadvantages:
(1) It is not suitable for users to perform complex data conversion and verification.
(2) Limited error handling capability

3 BCP example
(1) copy the SQL server data to a file
C: \> BCP dB .. table out sample.txt-S server-t-c
Starting copy...
63 rows copied.
Network packet size (bytes): 4096
Clock Time (Ms.) Total: 46 average: (1369.57 rows per sec .)
(2) copy the data from file to SQL Server
C: \> BCP dB .. xmltransfer in xt.txt-S server-t-c
Starting copy...
3 rows copied.
Network packet size (bytes): 4096
Clock Time (Ms.) Total: 93 average: (32.26 rows per sec .)
(3) copy the SQL server data to a file, specify the coloumns
C: \> BCP "select field from DB .. table" queryout sample.txt
-S server-t-c
Starting copy...
63 rows copied.
Network packet size (bytes): 4096
Clock Time (Ms.) Total: 15 average: (4200.00 rows per sec .)
(4) Create format XML file
C: \> BCP dB .. table format null-S server-t-c-X-F sample. xml

4. Several common parameters:-T,-R,-F

5. Special introduction-H Parameters
(1) scope of use: effective when importing data, can improve performance and reduce log records
(2) tablock: when using Table lock to import data
(3) Order: indicates that the source data is sorted by a field. If this field is consistent with the database table's crude index, it will help improve the performance during the import process. Otherwise, this parameter will be ignored during execution.

Iii. BULK INSERT
1 BCP vs bulk BULK INSERT
(1) bulk insert can only be used to import data. BCP can be used to import and export data.
(2) bulk insert is executed inside SQL, BCP runs outside SQL, and communicates with SQL Server through ICP
(3) BCP operations are also recommended for BULK INSERT

2 Comparison of three common parameters
(1)-T,-R,-F
(2) fieldterminator, rowterminator, firstrow

3. bulk insert permission
(1) In Windows Authentication mode, verify that the Windows account has the permission to operate files to determine whether bulk insert can be executed.
(2) When the mixed Authentication mode is used, if the SQL account that does not match the Windows account is used, the SQL service account is verified to have permission for file operations.

4 practice
Bulk insert xmltransfer from 'C: \ sample.txt 'with (datafiletype = 'widechar', rowterminator = ',')