SQL Server data import and export BCP tool usage details

Data import and export is one of the common tasks of database administrators, especially the import and export of flat files. The BCP tool provides strong support for these tasks. It is based on DB-library, especially in the production environment, to transmit data locally to the server or from the server to the local, because it does not need to provide a graphical interface, it reduces network bandwidth and improves the transmission rate. The full name of BCP is bulk Copy Program, which is a command line program and can be implemented completely out of the SQL server process.

Common import methods: BCP, bulk insert, OpenRowSet, or SSIs.

 

This document describes how to use the BCP tool. For other tools such as bulk insert, OpenRowSet, or SSIs, see the subsequent blog.

Use of BCP: large-capacity data replication can be performed between the SQL Server 2005 instance and data files in the specified format, and flat files can be imported to the SQL Server table, you can also export the SQL Server table as a file. This command is a DOS command, which is usually located in the X:/program files/Microsoft SQL Server/90/tools/bin directory and can be used at a command prompt.

 

The syntax is briefly listed as follows:
Syntax: BCP {[[database_name.] [owner].] {table_name | view_name} | "query"} -- specify the database name, table name, view name, or SQL query statement. The query statement is enclosed in double quotation marks.

{In | Out | queryout | format} data_file -- data flow direction, in import, out export, queryout result set, and specified data_file file.
[-Mmax_errors] [-fformat_file] [-x] [-eerr_file] -- BCP error handling options
[-Ffirst_row] [-llast_row] [-bbatch_size] -- you can specify a specific number of rows.
[-N] [-C] [-N] [-W] [-V (60 | 65 | 70 | 80)] [-6] -- about character encoding options, rarely used
[-Q] [-c {ACP | OEM | raw | code_page}] [-tfield_term]
[-Rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
[-Sserver_name [/instance_name] [-ulogin_id] [-ppassword] -- specify the login server name, Instance name, and login account password.
[-T] [-V] [-R] [-K] [-E] [-h "hint [,... n] "] -- hint prompts to use tablock, check_constraints, fire_triggers, and so on, which are usually used to support minimum Logging

Several common parameters:-F format_file

Format_file indicates the format file name. This option depends on the preceding action. If in or out is used, format_file indicates an existing format file. If format is used, it indicates a format file to be generated.

-X must be used with-F format_file to generate an XML format file.

-F first_row specifies which row of the exported table to export or which row of the imported file to import.

-L last_row specifies the row to which the exported table is to be imported or the end of data import from the imported file.

-C uses the char type as the storage type, with no prefix and "/T" as the field delimiter, and "/N" as the row delimiter.

-W is similar to-C. It is used only when the Unicode character set is used to copy data, and nchar is used as the storage type.

-T field_term specifies the delimiter. The default Delimiter is "/t ".

-R row_term specifies the row delimiter. The default value is "/N ".

-S SERVER_NAME [/instance_name] specifies the instance of the SQL Server server to be connected. If this option is not specified, BCP connects to the default instance of the local SQL Server. To connect to the default instance on a machine, you only need to specify the machine name.

-U login_id specifies the username used to connect to the SQL server.

-P password specifies the username and password used to connect to SQL Server.

-T indicates that BCP uses a trusted connection to log on to SQL Server. -U and-P must be specified if-T is not specified.

-K specifies that the empty column is inserted with a null value, rather than the default value of this column.

Permission:
The BCP out operation requires the select permission on the source table.
The BCP in operation requires at least select/Insert permission on the target table.
When the imported table has check constraints and triggers, the default action is to close. do not specify the-H option, check_constraints, and fire_triggers prompts. Therefore, you must have the alter table permission on the table.

1. Export bcp to a flat file
1. Copy the table to a flat file (using trusted connection and parameter-T), and enter the following statement at the command prompt:

BCP adventureworks. Sales. salesorderheader out d:/salesorders.txt-c-t ---T indicates that a trusted connection is used.

Starting copy...
1000 rows successfully bulk-copied to host-file. Total occupied ed: 1000
1000 rows successfully bulk-copied to host-file. Total occupied ed: 2000
-- ...... Omitted
-- ...... Omitted
31465 rows copied.
Network packet size (bytes): 4096
Clock Time (Ms.) Total: 454 average: (69306.17 rows per sec .)

 

The xp_cmdshell stored procedure is used to execute BCP. In view of the convenience of the demonstration, all subsequent processing will be implemented using xp_cmdshell.
Exec xp_cmdshell 'bcp adventureworks. Sales. salesorderheader out d:/salesorders1.txt-C-t'

 

2. copy the table to a flat file (using mixed authentication, using the-u-p parameter, for example,-U "test"-P "test ", -The user name after U and the password after-P are added with double quotation marks)
Exec xp_cmdshell 'bcp adventureworks. Sales. salesorderheader out d:/salesorders2.txt-c-u "test"-P "test "'

 

3. Copy the specified column or row to a flat file.
Exec xp_export shell -- export the specified column to use queryout
'Bcp "select salesorderid, orderdate, salesordernumber from adventureworks. Sales. salesorderheader" queryout D:/salesorders3.txt-c-u "test"-P "test "'

 

Exec xp_export shell -- export the specified row, and use queryout
'Bcp "select top (50) salesorderid, orderdate, salesordernumber from adventureworks. sales. salesorderheader where orderdate> = '000000' "queryout D:/salesorders4.txt-C-F 20-l 40-t'

 

Ii. BCP export format file
Format files are classified into common format files and XML format files. The following example forms a general format file, also known as a non-XML format file, in the format of the salesorderheader table.
Exec xp_cmdshell 'bcp adventureworks. Sales. salesorderheader format NUL-f d:/salesorders_format.fmt-C-t'

----------------
9.0
27 -- total number of fields. The extra fields are ignored. The following lists the serial numbers, types, lengths, delimiters, and field names of fields.
1 sqlchar 0 12 usd/t "1 salesorderid ""
2 sqlchar 0 5 rows /t "2 revisionnumber ""
3 sqlchar 0 24 usd/t "3 orderdate ""
4 sqlchar 0 24 usd/t "4 duedate

""
XML Format File
Exec xp_cmdshell 'bcp adventureworks. Sales. salesorderheader format NUL-X-f d:/saorders_format_x.xml-C-t'

The non-XML format file and the XML format file use different methods to describe the structure of the original table. The essence is the same.

 

BCP imports flat files to database tables
Create a new table neworderheader and import the exported data to the new table.

Select * into neworderheader from sales. salesorderheader where 1 = 2
Exec [Master] .. xp_mongoshell 'bcp adventureworks... neworderheader in D:/salesorders.txt-C-t'
Select * From neworderheader

 

Large-capacity BCP import using formatted files

Truncate table neworderheader
Exec [Master] .. xp_mongoshell 'bcp adventureworks... neworderheader in D:/salesorders.txt-f d:/currency. XML-F 2000-l 4000-C-t'
Select * From neworderheader