Learn about BCP Utility in SQL Server along with Command Line Syntax
BCP, which stands for Bulk Copy Program (BCP) is basically, a command line tool, which is used to import & export a huge amount of database in SQL server without difficulty. Database Administrator (DBA), who has accessed this utility, will have the same opinion that a bulk copy program is a vital tool. The utility can be used without any knowledge of Transact-SQL. With the help of BCP Utility in SQL server, users can rapidly import the huge database in SQL server table or export the data from the database table to data files. In the following section, we will discuss the attributes of BCP Tool.
Learn More- How does SQL Database Work
Attributes Used In SQL Server BCP Utility
These are the arguments used in BCP Tool:
database_name –Name of the database in which specified table reside comes under database_name.
In, out, queryout, and format, these things perform different functions as mentioned below:
- In– It replicates the data to the database table from the files
- Out– It replicates the data to data file from table of database
- queryout– It is used to copy the data from the query.
Format– It generates a file based on specified options such as -n,-c,-w. Producing XML file format, this option also needs -f option. It indicates with value nul.
data_file: It is the whole path of the data file. The path can consist of characters from 1 to 255. The data file can have 2^63-1 rows utmost.
table_name: It is the destination table’ name when the user import data to SQL Server (in) & the source table when user export data from SQL Server database (out)
view_name: It is the name of the source or destination view during importing data into a SQL Server database or exporting the data from SQL Server.
-a packet_size: It indicates the number of per network packet, sent to & from SQL Server.
-b batch size: It indicates the number of rows present in the data file for each batch, which is imported.
-c: It is useful in performing the operation with the help of character data type. It is not compatible with -w.
– C{ ACP| OEM|RAW|code_page}: It specifies the code page of data within a data file.
-d database: It describes the database to which established the connection.
-e err_file: It decides the entire path of an error file, which helps to accumulate or store any row that cannot be transferred by the BCP tool to the SQL server database.
-E: In the data file, it specifies identity values. The data file is imported & used for the identity column
-f format_file: It indicates the entire path of format file.
-F first_row: It decides the first row to export from a database table/import from a data file.
-h “hint[….n]”: It describes the hints which were used at the time of import of data in batches into a database table.
-i input_file: It indicates the response file name.
-k: It specifies the column, which is empty should have any default values or a null value during the operation.
-K application_intent: During the connection with the server, it shows the application workload type.
-L last_row: It indicates the last row, which is exported from SQL Server database table or import from the data file.
-m max_errors: It specifies the maximum number of errors that occur before BCP utility operation is canceled.
-n: With the help of native data, it performs the bulk copy operation
-N: It is useful in executing the copy operation in bulk with the help of the local data types of the data for the characters that are Unicode and for non-character data.
-o output_file: It indicates the name of the file, which obtains output from the command prompt.
-P password: It indicates the login ID’s password
-q: It helps to perform the set statement in the connection between SQL Server and the BCP utility. This option is helpful for the specification of a database, table name that has a single quotation mark or space, and owner.
-r row_term: It indicates the row terminator.
-R: It determines the data, currency, and time at which bulk data is copied to SQL server with the help of the regional format.
-S server_name[\instance_name]: It indicates the instance of SQL Server.
-t field_term: It specifies the field terminator.
-T: It indicates that the bulk copy utility connects to SQL Server with a reliable connection with the help of integrated security.
-U login_id: It shows the login ID used for the connection with SQL server.
-v: It shows the number of the version & copyright of BCP application.
-V(80|90|100|110|120|130): It performs the bulk copy operation via data types from an earlier version of SQL Server.
80= SQL Server 2000
90= SQL Server 2005
100= SQL Server 2008 & SQL Server 2008 R2
110= SQL Server 2012
120= SQL Server 2014
130= SQL Server 2016
-w: It executes the copy operation in the bulk via Unicode characters.
-x: It is used with the format & options of -f format_file and creates an XML-based format file.
Conclusion
The BCP utility in SQL Server is considered as one of the essential utility for exporting data in bulk from SQL Server table into data files. It also imports data in bulk from a data file to a database table. Therefore, in the above section, we have discussed the syntax along with arguments, that is used in SQL server BCP utility.