SQL Server BCP Data Import Export

SQL Server BCP Import Export uses

BCP Export Import data is efficient, and is more efficient than using the database exports provided by SQL Server Management Stdio, because SQL Server also does not provide tools that provide Oracle-like EXPDP and IMPDP, if you use SQL Server Management Stdio provides import and export migration of large table data is too slow

BCP Export Data Efficient comparison for large table data migration

Enter the bcp command on the doc command line to eject the help information for the bcp command:

C:\users\wangwei>bcp

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

[-m maximum number of errors]       [-F Format File] [-e Error file]

[-F First line]             [-L-End line] [-B Batch Size]

[-N Native Type]         [-C character type] [-W wide character type]

[-N to keep non-text as native type]     [-V file format version] [-Q quoted identifier]

[-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 locale settings]

[-K reserved Null value] [-e reserved Identity value]

[-H ' load hint '] [-x generate XML format file]

Parameter description:

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 that needs to be imported.

(2) export.

This action is done with the Out command, followed by the name of the file that needs to be exported.

(3) Export using SQL statement.

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 an SQL statement.

(4) Export format file.

This action is done using the Format command, followed by the file name.

Here are some common options:

-F Format_file

Format_file represents the format file name. This option relies on the above action if you are using in or out,format_file to indicate that a format file already exists, and if you are using format, it means the format file to be generated.

-X

This option is used in conjunction with-F format_file to generate a format file in XML format.

-F First_row

Specifies which row of the exported table to export from, or which line of the imported file to import.

-L Last_row

Specifies the end of the row to which the exported table is to be directed, or the line to which to import the data from the imported file.

-C

Use the char type as the storage type, without a prefix and with "\ T" as the field separator, with "\ n" as the line separator.

-W

Similar to-C, only used when copying data using the Unicode character set, and nchar as the storage type.

-T Field_term

Specifies the character delimiter, which is "\ T" by default.

-R Row_term

Specifies the row separator, which defaults to "\ n".

-S server_name[\instance_name]

Specifies the 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 the default instance on a machine, you only need to specify the machine name.

-U login_id

Specify the user name of the connection SQL Sever, the user name directly between the parameters do not have spaces, or will be error

As user name: Fred, the correct format is:-ufred

-p password

Specify the user name password to connect to SQL Server, and do not have a space between the password and the parameter.

-T

Specifies that BCP uses a trusted connection to log on to SQL Server. If-T is not specified, you must specify-U and-P.

-K

Specifies that an empty column is inserted using a null value instead of the default value for this column.

Export Data Using bcp

To perform bcp on the console:

BCP db_name.dbo.T_tablename out c:\a.txt-c-t

bcp db_name.dbo.T_tablename out c:\a.txt-c-uusername-pxxxxx

Run bcp as an SQL statement by calling a system stored procedure in SQL Server xp_cmdshell:

EXEC master. xp_cmdshell ' bcp db_name.dbo.T_tablename out c:\a.txt-c-t '--"t" trusted connection

EXEC master. xp_cmdshell ' bcp db_name.dbo.T_tablename out c:\a.txt-c-usa-pxxxxx '

Note: If there is a space in the middle of a parameter, enclose it in double quotation marks, not two single quotes.

Description

-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.

If you do not use T, you must add the-U and-p parameters to enter the user name and password

If the local import export can be without the-s parameter, if you connect to the remote service, you need to add the-s parameter

bcp db_name.dbo.tablename out d:\table.txt-c-q-s "120.26.76.220,1833"-usa-ppassword

Filter the tables that you want to export.

BCP can not only accept table names or view names as parameters, but also accept SQL as arguments, and SQL statements allow you to filter the tables to be exported and then export the filtered records.

EXEC Master. xp_cmdshell ' bcp ' select TOP $ * from Db_name.dbo.T_tablename "queryout c:\table2.txt-c-U" sa "-P" password "'

BCP can also restrict the exported rows by simply setting options.

This command uses two parameters-F 2 and-L 16, which represent the SE

EXEC Master. xp_cmdshell ' bcp ' select TOP $ * from db_name.dbo.T_tablename "queryout C:\currency2.txt-F 2-l 16-c-U" sa "-P" password "‘

SELECT TOP * from Db_name.dbo.T_tablename the results found in the 2nd to 16 records to export.

Importing data Using bcp

BCP can import bcp exported files into a table by using the in command, and first you need to create the table structure and export the data in the same table structure

To perform bcp on the console:

BCP db_name.dbo.T_tablename in C:\a.txt-c-t

BCP db_name.dbo.T_tablename in C:\a.txt-c-uusername-pxxxxx

Run bcp as an SQL statement by calling a system stored procedure in SQL Server xp_cmdshell:

EXEC master. xp_cmdshell ' bcp db_name.dbo.T_tablename in c:\a.txt-c-t '--"-T" trust connection

EXEC master. xp_cmdshell ' bcp db_name.dbo.T_tablename in c:\a.txt-c-usa-pxxxxx '

SQL Server BCP Data Import Export