SQL Server bulk Data export Import bcp uses

About BCP

BCP is a command-line tool in SQL Server that is responsible for importing and exporting data, is db-library-based, 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 you can filter a table or view by using the Select from statement. When importing and exporting data, you can use a default value or use a format file to import data from a file into a database or export data from a database to a file

BCP execution mode

1. BCP executes from the console command line
2. Run as an SQL statement by calling a system stored procedure for SQL Server xp_cmdshell

* Note: You need to enable xp_cmdshell via xp_cmdshell mode

EXEC sp_configure ' show advanced options ', 1
GO
RECONFIGURE
GO
EXEC sp_configure ' xp_cmdshell ', 1
GO
RECONFIGURE
GO

Parameter description for 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 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
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 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.

* * Command format: 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] [-e reserved Identity value]
[-H ' load hint '] [-x generate XML format file]

Export Data Using bcp

1. Use bcp to export the entire table or view.

--command line execution with password connection

BCP OEDB.dbo.kqdata out c:\a.txt-c-S "XXX.XXX.XXX.XXX"-u "sa"-P "password"

BCP OEDB.dbo.kqdata out c:\A.txt-c-S "XXX.XXX.XXX.XXX"-t--Using trusted connections

--sql Statement Mode execution

EXEC Master. xp_cmdshell ' bcp oedb.dbo.kqdata out c:\a.txt-c-u "sa"-P "password" '

2. Export filtered result data

EXEC Master. xp_cmdshell ' bcp ' select TOP * from Oedb.dbo.kqdata where kqdata< ' 2016-01-01 ' "Queryout c:\a.txt-c-U" sa "-P" pas Sword "'

--Take 10th to 13 records to export

EXEC Master. xp_cmdshell ' bcp ' select TOP $ * from Oedb.dbo.kqdata "queryout C:\a.txt-F 10-l 13-c-U" sa "-P" password "

--Export as CSV
Exec Master. xp_cmdshell ' bcp ' oedb.dbo.kqdata "out" C:\a.csv "-c-t", "-r" \ n "-t"

3. Export format files using bcp

BCP can not only import and export data based on tables, views, but also work with format files to restrict import and export data. Format files exist as plain text files, and are divided into general and XML formats. 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 oedb.dbo.kqdata format nul-f c:\a_format.fmt-c-T '

--The above command generates a format file for the structure of the Kqdata table a_format.fmt

BCP can also generate format files in XML format with the-X option.
EXEC Master. xp_cmdshell ' bcp oedb.dbo.kqdata format nul-f c:\a_format2.fmt-x-c-t '
The XML format file describes exactly the same content as the plain format file, except that the format is different

4. Importing data Using bcp

BCP can then re-import the a.txt exported above into the database using the in command
Import data into the Kqdata table
EXEC Master. xp_cmdshell ' bcp ohr.dbo.kqdata in c:\a.txt-c-T '
Importing data can also use the-F and-l options to select the row of records to import data into.
EXEC Master. xp_cmdshell ' bcp ohr.dbo.kqdata in c:\a.txt-c-F 10-l 13-t '
When importing data, you can import a record that satisfies a condition into a database based on a format file that already exists and not be imported if it is not satisfied.
Use plain format files
EXEC Master. xp_cmdshell ' bcp ohr.dbo.kqdata in C:\a.txt-F 10-l 13-c-F C:\a_format.fmt-T '
Format file using XML format
EXEC Master. xp_cmdshell ' bcp ohr.dbo.kqdata in C:\a.txt-F 10-l 13-c-x-f C:\a_format2.fmt-T '

Import CSV format file
Exec Master. xp_cmdshell ' bcp ' ohr.dbo.kqdata "in" C:\a.csv "-c-t", "-r" \ n "-t"

SQL Server bulk Data export Import bcp uses