SQL Server BCP command usage and data batch Import and Export SQL Server BCP use summary a community 6 million user data import MySQL, MSSQL, Oracle Database methods a community 6 million user data import mysq

Document directory

• 2. 1. Export data from the table to a file (using trusted connections)
• 2. export data from the table to a file (using Hybrid Authentication)
• 2. 3. Import the data in the file to the table

0. References:

SQL Server BCP usage Summary

BCP Utility

How to import data from 6 million users to MySQL, MSSQL, and Oracle databases in a community

Select into and insert into select table copy statements

1. BCP Command Parameter Parsing

The BCP command has many parameters. Below is a brief analysis of the BCP command parameters.

Usage: BCP {dbtable | query} {in | Out | queryout | format} data file [-m maximum error count] [-F format file] [-e error file] [-f First Line] [-l last line] [-B batch size] [-n local type][-C character type][-W wide character type] [-N retains non-text as the local type] [-V file format version] [-Q quoted identifier] [-C code page description] [-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 region settings] [-K reserved null values] [-e reserved ID values] [-h "loading prompt"] [-x generated XML formatting file]

The most common ones are marked in pink.

2. BCP command instance

Here we use the adventureworks sample database as an example to conduct an experiment.

2. 1. Export data from the table to a file (using trusted connections)

bcp AdventureWorks.Sales.Currency out c:\Currency.dat -T -c

The above parameter out indicates the output file, c: \ currency. dat indicates the file name and path, and-T indicates a trusted connection. This is a little different from sqlcmd. In sqlcmd,-e Indicates a trusted connection. -C indicates output in the form of characters. If-W is used, the output content is the same, but the size of the output file will be doubled. If you want to import the exported currency. dat file to a non-SQL Server database, it is better to use-w.

2. export data from the table to a file (using Hybrid Authentication)

bcp AdventureWorks.Sales.Currency out c:\Currency.dat -c -Usa -Psa12345 -S.

This data export statement differs from the previous one in that it uses a trusted connection, that is, Windows authentication, and does not require a user name or password. In this case, the SQL Server authentication method is used. Therefore, you must enter the SQL Server database user name and password. Here-s indicates the data source to be connected. Here I am-s. connect to the local default instance. If you do not add the-S parameter, it will be connected to the default instance. to connect to the named instance, you can use the parameter-S <SERVER_NAME \ instance_name>

2. 3. Import the data in the file to the table

Mentioned in BCP utility:

"If you useBCPTo back up data, create a formatting file to record the data format.BCPThe data file does not contain any schema or format information. Therefore, if the table or view has been deleted and the formatted file is not available, the data may not be imported ."

If you want to use bcp to back up data, it is best to export the table structure along with the data. In this way, even if the table is deleted, you can create a table first and then use the BCP method to import data for restoration. However, if you only use bcp to back up data without backing up the table structure, you will not be able to use bcp to import data after the table is deleted.

In the deeper layer, if you want to use bcp to import data, you must have a table structure. This is similar to the copy operation of insert into select, because it also needs to create a table before data backup. For details, see select into and insert into select table copy statements.

So if we want to export the previously exported currency. when dat is imported to the database, the database must have a corresponding table. Here we create a table named sales. the SQL statement is as follows:

Use adventureworks; goselect * into sales. currency2 from adventureworks. Sales. Currency where 1 = 2; -- only create a table structure without inserting data

After creating the table structure, you can import the data in the local file to the database table. The imported BCP command is as follows:

bcp AdventureWorks.Sales.Currency2 in c:\Currency.dat -T -c

2.4.use the queryout keyword in BCP

You can use the queryout keyword to export data based on certain conditions.

2.4.1. Copy a specific column to a data file

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout c:\Currency.Name.dat -T -c

2.4.2. Copy a specific row to a data file

bcp "select * from AdventureWorks.Sales.Currency where CurrencyCode='AED' and Name='Emirati Dirham'" queryout c:\Currency3.dat -T -c

3. Batch import of large data volumes

BCP itself can be used for batch import of large data volumes, but it is in the command line format. If you want to use SQL commands for batch import of large data volumes, you can use BULK INSERT, this is mentioned in a previous blog and has conducted experiments. For details, refer to: How to import data from 6 million users to MySQL, MSSQL, and Oracle databases in a community. I also found a blog that lists bulk insert and BCP batch import methods. For details, see SQL Server BCP usage summary.

BCP can be executed not only in the CMD command line, but also in the SQL query statement. However, a stored procedure needs to be called. For example, if you export data from a database table to a file, you can execute the following SQL statement in SSMs:

exec master..xp_cmdshell 'bcp AdventureWorks.Sales.Currency out c:\Currency.dat -T -c'

If you encounter the following error when executing the preceding statement:

SQL Server blocked access to procedure 'sys. xp_expose shell 'of component 'xp _ expose shell' because this component is turned off as part of the security configuration for this server. a system administrator can enable the use of 'xp _ Your shell' by using sp_configure. for more information about enabling 'xp _ Your shell', see "surface area configuration" in SQL Server books online.

You can solve the problem by executing the following statement: refer to: xp_cmdshell Option

-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGO