The Bulk Copy Program (BCP) is a command line tool, which is used to import and export a large amount of data in SQL server database easily. Any Database Administrator (DBA), who has used this utility will agree that the bulk copy program is an essential tool. This utility does not require knowledge of Transact-SQL. With the help of BCP utility user can quickly import large amount of data in SQL server table or export data from database table to data files. This tool is also used to generates format files.
SQL Server BCP Command Line Syntax
Arguments Used In SQL Server BCP Tool
database_name: It is the name of database in which specified table resides.
in, out, queryout, and format nul specifies the direction of bulk copy as describe below:
- in- It copies data from data file to database table.
- out- It copies data from database table to data file.
- queryout- It copies data from query.
- format- It creates a format file based on specified options like -n,-c,-w. Creating XML file format, this option also requires -f option. It specify with value nul.
data_file: It is the full path of data file. The path can contain 1 to 255 characters and the data file can have maximum 2^63-1 rows.
table_name: It is the name of the destination table when user import data to SQL Server database (in), and the source table when user export data from SQL Server (out).
view_name: It is the name of destination or source view when importing data into a database or exporting data from SQL server database.
-a packet_size: It defines the number of bytes, per network packet, sent to and from the SQL serve.
-b batch size: It specifies the number of rows in data file per batch of imported data.
-c: It performs the operation by using character data type. It is not compatible with -w.
- C{ ACP| OEM|RAW|code_page}: It determines the code page of data in the data file.
-d database: It define the database to connect to.
-e err_file: It determines the full path of an error file which is used to store any rows that the BCP tool cannot transfer the file to the SQL server database.
-E: It define identity values in the imported data file that are used for the identity column.
-f format_file: It defines the full path of format file.
-F first_row: It determines the first row to export from a database table or import from a data file.
-h "hint[….n]”: It defines the hints which were used during a bulk import of data into a database table.
-i input_file: It specifies the response file name.
-k: It defines that empty column should have a null value or any default values during the operation.
-K application_intent: when connecting to the server, it declares the application workload type.
-L last_row: It specifies the last row that export from SQL sever database table or import from a data file.
-m max_errors: It defines the maximum number of errors that arise before BCP utility operation is canceled.
-n: It executes the bulk copy operation by using native data types of data.
-N: It Performs the bulk copy operation by using the native data types of the data for Unicode characters for character data and for non-character data.
-o output_file: It defines the file name which receives output from the command prompt.
-P password: It specifies the password for login ID.
-q: It executes the set statement in the connection between the BCP utility and SQL Server. This option can be used to specify a database, owner, table name that have a single quotation mark or space.
-r row_term: It defines the row terminator.
-R: It specifies the currency, data and time at which bulk data is copied to SQL server database by using the regional format.
-S server_name[\instance_name]: It determines the instance of SQL Server. If no server is specified then BCP tool connects to the default instance of SQL Server on the local machine.
-t field_term: It defines the field terminator.
-T: It defines that the bulk copy program utility connects to SQL Server with a trusted connection by using integrated security.
-U login_id: It determines the login ID used to connect to SQL server.
-v: It reports the version number and copyright of BCP tool.
-V(80|90|100|110|120|130): It executes the bulk copy operation by using data types from an older or earlier version of SQL Server.
80= SQL Server 2000
90= SQL Server 2005
100= SQL Server 2008 and SQL Server 2008 R2
110= SQL Server 2012
120= SQL Server 2014
130= SQL Server 2016
-w: It Performs the bulk copy operation by using Unicode characters.
-x: It is used with the format and -f format_file options and generates an XML-based format file.
Importing And Exporting Bulk Data With SQL Server BCP Utility
The BCP tool can export data from SQL server database table into a data file to use in other program and it can also import data into SQL server database table from another database management system. With the help of BCP utility, data can be import or export in or out from SQL server database easily and quickly.
Conclusion
The BCP utility in SQL Server is one of the best tool to export bulk data from SQL server database table into data files or to import bulk data into database table from a data file. In this blog, we have discussed syntax and all arguments used in BCP command line utility.
EmoticonEmoticon