SQL Server BCP (data Import Export tool) General usage and commands detailed _mssql

BCP is a command-line tool in SQL Server that is responsible for importing exported data, is based on db-library, and can efficiently import and export large quantities of data in a parallel manner. BCP can export a table or view of a database directly, or it can filter the table or view through a select from statement. When you import export data, you can use default values or use a format file to import data from a file into a database or to export data from a database to a file. The following is a detailed discussion of how to import export data using bcp.

1. Introduction to main parameters of BCP

BCP has a total of four actions to choose from.
(1) Import.
This action is done using the in command followed by the name of the file you want to import.
(2) export.
This action is done using the Out command followed by the name of the file you want to export.
(3) Export using SQL statements.
This action is done using the Queryout command, which is similar to out, except that the data source is not a table or view name, but rather an SQL statement.
(4) Export the format file.
This action is done using the Format command, followed by the file name.
The following are some common options:
-F Format_file
Format_file represents the format file name. This option depends on the action described above, if you are using in or Out,format_file to indicate a format file that already exists, and if you are using format, it means the file you want to generate.
-X
This option is used in conjunction with-F format_file to generate XML-formatted format files.
-F First_row
Specifies which row from the exported table is exported, or from which line of the file being imported.
-L Last_row
Specifies which line to end when the exported table is to be directed to, or from the file being imported.
-C
Use the char type as the storage type, with no prefix and "\ t" as the field delimiter, and "\ n" as the row delimiter.
-W
Similar to-C, only used when copying data using the Unicode character set, and nchar as the storage type.
-T Field_term
Specifies a character delimiter, which defaults to "\ T".
-R Row_term
Specifies the line delimiter, which is "\ n" by default.
-S server_name[\instance_name]
Specifies an instance of the SQL Server server to which you want to connect, and if this option is not specified, bcp connects to the native SQL Server default instance. If you want to connect to a default instance on a machine, simply specify the machine name.
-U login_id
Specifies the user name to connect to SQL Sever.
-p password
Specifies the user name password to connect to SQL Server.
-T
Specifies that bcp log on to SQL Server using a trusted connection. If-T is not specified, you must specify-U and-P.
-K
Specifies that empty columns are inserted using null values instead of the default values for this column.

2. How to export data using bcp

(1) Export the entire table or view using bcp.
BCP AdventureWorks.sales.currency out c:\currency1.txt-c-u "sa"-P "password"--use password connection or bcp AdventureWorks.sales.currency out C:\currency1.txt-c-t--Using trusted connections
The following is the output of the command executed above
Starting copy ...
Copied rows.
Network packet Size (bytes): 4096
Clock time (Ms.) Total:10 Average: (10500.00 rows per sec.)
Here are some of the currency1.txt
AED Emirati dirham 1998-06-01 00:00:00.000
AFA Afghani 1998-06-01 00:00:00.000
... ... ...
... ... ...
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000
When you log in using a password, you need to enclose the-u username and the post-p password with double quotes.
Note: In addition to being able to execute in the console, BCP can also run bcp in SQL statements by calling a system stored procedure of SQL Server xp_cmdshell. If the first command above can be rewritten as
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency out c:\currency1.txt-c-u ' sa '-p ' password '
After the xp_cmdshell is executed, the return information is output as a table. To facilitate the execution of BCP in SQL, the following commands use xp_cmdshell to execute bcp commands.
(2) Filter the tables to be exported.
BCP can accept not only the table name or view name as a parameter, but also SQL as a parameter. SQL statements allow you to filter the tables that you want to export, and then export the filtered records.
EXEC Master.. xp_cmdshell ' bcp ' select top * ' AdventureWorks.sales.currency ' queryout c:\currency2.txt-c-u ' sa '-p ' password '
BCP can also restrict the exported rows by simply setting the options.
This command uses two parameters-F 10 and-L 13, indicating that the SE
EXEC Master.. xp_cmdshell ' bcp ' select top * AdventureWorks.sales.currency ' queryout c:\currency2.txt-F 10-l-u ' sa ' P ' p Assword "'
The results from the lect top * from AdventureWorks.sales.currency are derived from 10th to 13 records.

3. How to export a format file using bcp

BCP can not only import export data based on tables and views, but also restrict import export data with a format file. The format file exists as a plain text file, divided into general format and XML format. Users can write format files by hand, or they can automatically generate format files from tables and views by using bcp commands.
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency format nul-f c:\currency_format1.fmt-c-T '
The above command generates a format file CURRENCY_FORMAT1.FMT the structure of the Currency table, and the following is the contents of the format file.
9.0
3
1 SQLCHAR 0 6 "T" 1 CurrencyCode sql_latin1_general_cp1_ci_as
2 SQLCHAR 0 "T" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 "\ r \ n" 3 ModifiedDate
This format file records information such as the field (3 fields) of this table, length, character, and row break and field name.
BCP can also generate XML-formatted format files through the-X option.
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency format nul-f c:\currency_format2.fmt-x-c-t '
The content described in the XML format file is exactly the same as what is described in the normal format file, except in a different format.

4. How to import data using bcp

BCP can then re-import the Currency1.txt and currency2.txt exported above to the database through the in command, and because currency has a primary key, we will copy a table that is exactly the same as the currency structure.
SELECT Top 0 * to AdventureWorks.sales.currency1 from AdventureWorks.sales.currency
Import data into the Currency1 table
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency1 in c:\currency1.txt-c-T '
The import data can also use the-F and-l options to select the row of records to import data.
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency1 in c:\currency1.txt-c-F 10-l 13-t '
When you import data, you can import records that meet the criteria to the database based on the existing format file, and are not imported if they are not satisfied. If the character length of the third field in the above format file is 24, if the corresponding field in a text file is longer than 24, the record will not be imported into the database, and other records that meet the criteria are imported normally.
Use a normal format file
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency1 in C:\currency1.txt-F 10-l 13-c-F C:\currency_format1.fmt-T '
Format files using XML format
EXEC Master.. xp_cmdshell ' bcp AdventureWorks.sales.currency1 in C:\currency1.txt-F 10-l 13-c-x-f '

Summary

The bcp command is a Quick data import export tool provided by SQL Server. Using it does not require you to start any graphics management tools to import the exported data in an efficient manner. Of course, it can also be executed in an SQL statement by xp_cmdshell, which can be run in a client program such as Delphi, C #, and so on, which is one of the ways that the client program has data import and export capabilities.

bcp command detailed (SYBASE)

The bcp utility replicates data in a user-specified format between Microsoft SQL Server 2000 instances and data files.
Grammar
BCP {[database_name.] [owner].] {table_name | view_name} | "Query"}
{in | out | queryout | format} data_file
[M max_errors] [F Format_file] [-E Err_file]
[F First_row] [-L Last_row] [B batch_size]
[-N] [-c] [W] [-N] [-V (60 | 65 | 70)] [-6]
[Q] [-C Code_page] [-T Field_term] [R Row_term]
[I. Input_file] [-O output_file] [-A packet_size]
[-S Server_name[\instance_name]] [-U login_id] [-p password]
[-T] [-V] [-R] [K] [-E] [-H "hint [,... n]"]

Parameters
database_name

The name of the database that contains the specified table or view. If not specified, the user default database.

Owner

The name of the owner of the table or view. Owner is optional if the user performing the bulk copy operation has the specified table or view. If owner is not specified and the user performing the bulk copy operation does not own the specified table or view, then Microsoft? SQL Server? 2000 returns the error message and cancels the bulk copy operation.

table_name

Is the destination table name for copying data to SQL Server (in), and the name of the source table (out) When copying data from SQL Server.

View_name

is the name of the destination view for copying data to SQL Server (in), and the name of the source view (out) When copying data from SQL Server. Only a view of all columns referencing the same table can be used as a destination view. For more information about the limitations of copying data to views, see INSERT.

Query

is a Transact-SQL query that returns a result set. If a query returns multiple result sets, such as a SELECT statement that specifies a COMPUTE clause, only the first result set is copied to the data file, and subsequent result sets are ignored. Use double quotes to cause a query statement to use single quotes to cause any content embedded in a query statement. When you bulk copy data from a query, you must also specify Queryout.

In | Out | queryout | Format

Specifies the direction of bulk copy. In is copying from a file to a database table or view, out of which you copy from a database table or view to a file. You must specify queryout only when you bulk copy data from a query. Format creates a format file, depending on the option you specify (-N,-C,-W,-6, or-N), and the table or view separator. If you use format, you must also specify the-F option.



Explains that the bcp utility in Microsoft SQL Server 6.5 does not support bulk copying to tables that contain sql_variant or bigint data types.


Data_file

The full path of the data file used when bulk copy a table or view to disk (or copy from disk). When bulk copying data to SQL Server, this data file contains data that will be copied to the specified table or view. When you bulk copy data from SQL Server, the data file contains data that is copied from the table or view. The path can have 1 to 255 characters.

-M max_errors

Specifies the maximum number of errors that may occur before the bulk copy operation is canceled. Each row that bcp cannot copy will be ignored and counted as an error. If this option is not included, the default is 10.

-F Format_file

Specifies the full path of the format file that contains the storage responses that were previously used on the same table or view when using bcp. Use this option when bulk copying or copying of data using a format file created by the format option. The creation of a format file is optional. After several formatting questions are prompted, bcp prompts you to save the answer in the format file. The default file name is BCP.FMT. When bulk copying data, BCP can refer to a format file, so you do not have to re-enter the previous answer. If you do not use this option and you do not specify –n,-C,-W,-6, or-N, bcp prompts for the format information.

-E Err_file

Specifies the full path of the error file that is used to store all rows that bcp cannot transfer from the file to the database. Error messages from BCP are sent to the user station. If this option is not used, the error file is not created.

-F First_row

Specifies the ordinal number of the first row to bulk copy. The default value is 1, which indicates the first row in the specified data file.

-L Last_row

Specifies the ordinal of the last line to bulk copy. The default value is 0, which indicates the last row in the specified data file.

-B batch_size

Specifies the number of rows in each batch of data that is copied. Each batch is replicated to the server as a transaction. SQL Server commits or rolls back (on failure) each batch of transactions. By default, all data in the specified data file is replicated as a batch. Please do not use with the-h "rows_per_batch = bb" option.

-N

Perform bulk copy operations using the native (database) data type of the data. This option does not prompt for each field, and it will use native values.

-C

Performs bulk copy operations using character data types. This option does not prompt for each field; it uses char as the storage type, without a prefix, \ t (tab) as the field delimiter, \ n (newline character) as the line terminator.

-W

Performs bulk copy operations using Unicode characters. This option does not prompt for each field; it uses nchar as the storage type, without a prefix, \ t (tab) as the field delimiter, \ n (newline character) as the line terminator. cannot be used in SQL Server version 6.5 or earlier.

-N

Performs bulk copy operations on the native (database) data types that use data for non-character data and on character data using Unicode character types. This is a higher performance option than the-W option to transfer data from one SQL Server to another using a data file. It does not prompt for each field. You can use this option when you need to transfer data that contains ANSI extended characters and want to take advantage of native mode performance. You cannot use the-n option in SQL Server version 6.5 or earlier.

-V (60 | 65 | 70)

Perform bulk copy operations using data types from earlier versions of SQL Server. This option is used in conjunction with the character (-c) or native (-N) format. This option does not prompt for each field, and it uses the default value. For example, to bulk copy the date format supported by the bcp utility in SQL Server 6.5 (but ODBC no longer supports) to SQL Server 2000, you can use the-V 65 parameter.



Important When you bulk copy data from SQL Server to a data file, the date format for SQL Server 6.0 or SQL Server 6.5 is not generated for any datetime or smalldatetime data, even if the –V,BCP utility is specified 。 The date will always be written in ODBC format. Also, since SQL Server version 6.5 or earlier does not support nullable bit data, the null value in the bit column is written as a value of 0.


-6

Perform bulk copy operations using SQL Server 6.0 or SQL Server 6.5 data types. only to maintain backward compatibility. Use the –V option instead.

-Q

Executes the SET quoted_identifiers on statement in a connection to a bcp utility and an instance of SQL Server. Use this option to specify the name of the database, owner, table, or view that contains spaces or quotes. Enclose the entire three-part table name or view name in double quotes ("").

-C Code_page

only to maintain backward compatibility. Instead, specify a collation name for each column in the format file or in interactive bcp.

Specifies the data code page in the data file. Code_page is useful only if the data contains char, varchar, or text columns with a character value greater than 127 or less than 32.

code page Value Description
ACP ansi/microsoft Windows? (ISO 1252).
The default code page used by the OEM client. If-c is not specified, this is the default code page used by bcp.
RAW does not change from one code page to another. This is the quickest option because the conversion does not occur.
< value > Specific code page number, for example, 850.


-T Field_term

Specifies the field terminator. The default field terminator is \ t (tab). Use this parameter to override the default field terminator.

-R Row_term

Specifies the line terminator. The default line terminator is \ n (line break). Use this parameter to override the default line terminator.

-I. Input_file

Specifies the name of the response file that contains a response to a command prompt question for each field when bulk replication is performed using interactive mode (without specifying –n,-C,-W,-6, or-N).

-O output_file

Specifies the name of the file that receives bcp output (redirected from the command prompt).

-A packet_size

Specifies the number of bytes per network packet sent to and sent from the server. You can use SQL Server Enterprise Manager (or sp_configure system stored procedures) to set server configuration options. However, you can use this option to override server configuration options individually. The packet_size can be set to 4096 to 65535 bytes, and the default value is 4096.

Increased packet size can improve the performance of bulk copy operations. If a larger packet is required and is not available, the default setting is used. The performance statistics generated by BCP show the size of the packets used.

-S Server_name[\instance_name]

Specifies the instance of SQL Server to which you want to connect. Specify server_name to connect to the default instance of SQL Server on this server. Specify server_name\instance_name to connect to the SQL Server 2000 named instance on the server. If no server is specified, bcp connects to the default instance of SQL Server on the local computer. This option is required when performing bcp from a remote computer on the network.

-U login_id

Specifies the login ID to use to connect to SQL Server.

-p password

Specifies the password for the login ID. If this option is not used, bcp prompts for a password. If you use this option with the end of the command prompt without a password, BCP uses the default password (NULL).

-T

Specifies that BCP uses the security credentials of a network user to connect to SQL Server through a trusted connection. No need for login_id and password.

-V

Reports the version number and copyright of the bcp utility.

-R

Specifies that currency, date, and time data is bulk copied into SQL Server using the regional format defined for the client computer's locale. By default, regional settings are ignored.

-K

Specifies that empty columns in bulk copy operations should retain a null value instead of assigning a default value to the inserted column.

General usage of BCP

Usage: bcp {dbtable | query} {in | out | queryout | format} data file

[-M maximum error number] [-F Format File] [-e Error file]
[F First line] [End-L] [B-Batch size]
[n-Native type] [-C character type] [-W wide character type]
[-N Keep non-text to native type] [-V file format version] [-Q Quoted Identifiers]
[-C code page specifier] [-t field Terminator] [-R line Terminator]
[-I input file] [-O Output FILE] [-A packet size]
[-s server name] [-u user Name] [-p password]
[-T trusted connection] [-V Version] [-R allows regional settings to be used]
[k-Preserve NULL] [-e reserved Identity value]
[-H "Load Prompt"] [-X generation of XML format file]
Import CSV format file
Exec Master.. xp_cmdshell ' bcp ' SSIS.dbo.tb2 ' in ' E:\export.csv '-c-t ', '-R ' \ n '-t '
Export as CSV
Exec Master.. xp_cmdshell ' bcp ' SSIS.dbo.tb2 "out" E:\test.csv "-c-t", "-r" \ n "T"
Export a specific query to the default format
Default tab spacing, "\ n" line wrapping
Exec Master.. xp_cmdshell ' bcp ' select Carbrand,longitude from Ssis.dbo.tb2 ' queryout ' E:\test2.txt '-c-t '
Other case reference: http://msdn.microsoft.com/zh-cn/library/ms162802.aspx
Enable xp_cmdshell
EXEC sp_configure ' show advanced options ', 1
Go
Reconfigure
Go
EXEC sp_configure ' xp_cmdshell ', 1
Go
Reconfigure
Go