BCP (Bulk Copy Program) is a powerful tool used to import and export data in SQL Server. It allows you to efficiently transfer large amounts of data between SQL Server and files or other databases. In this article, we will explore the basics of BCP and how to use it effectively.
Requirements
In order to use BCP, you will need a local machine with BCP 13.1 or later installed. BCP is available for both Windows and Linux, but we will focus on the Windows version for this article. Additionally, you will need access to an Azure SQL Data Warehouse (ASDW) with a database such as Adventureworks LT.
Getting Started
Before we dive into the examples, let’s first check the version of BCP installed on your machine. Open the command prompt and run the following command:
bcp -vThe version number will indicate which version of SQL Server you have installed. For more information about different versions, you can refer to the official documentation.
Now, let’s look at an example of how to export data from an Azure SQL Data Warehouse table to a file using BCP:
bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\dates.dat -S sqlcentralserver.database.windows.net -U Daniel -P MySecretpwd012 -n –qIn this example, we are exporting the data from the “dbo.dimdate” table in the “sqlcentralwarehouse” database to a file named “dates.dat” on our local machine. The “-S” parameter specifies the Azure server name, “-U” specifies the username, “-P” specifies the password, “-n” performs the operation in native format, and “-q” handles quoted identifiers.
If everything is successful, you will find the exported file “dates.dat” in the specified directory on your local machine.
BCP with Prompt Password
In the previous example, the password was specified in plain text. However, for security reasons, it is recommended to avoid exposing passwords in plain text. Instead, you can prompt for the password during the BCP operation. Here’s an example:
bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\dates.dat -S sqlcentralserver.database.windows.net -U Daniel -n –qIn this example, we are not specifying the “-P” parameter for the password. Instead, BCP will prompt you to enter the password manually.
Working with Hints in BCP
BCP allows you to apply hints to control the behavior of the import or export operation. Hints can be used to specify the order of columns, the number of rows per batch, or to lock the table during the operation. Here’s an example of using the “TABLOCK” hint to lock the table while performing the BCP operation:
bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\dates.dat -S sqlcentralserver.database.windows.net -U Daniel -n -q -h TABLOCKThis hint can improve performance when dealing with large tables and multiple concurrent users accessing the table.
Limiting Rows in BCP
You can also limit the number of rows exported or imported using BCP. For example, to export only the first 10 rows of a table, you can use the “-L” parameter:
bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\firstten.dat -S sqlcentralserver.database.windows.net -U Daniel -n –q -L10This will export the data from the “dbo.dimdate” table until the 10th row and save it in the specified file.
Exporting Specific Rows
If you want to export a specific range of rows from a table, you can use the “-F” (First) and “-L” (Last) parameters. Here’s an example:
bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\firstten.dat -S sqlcentralserver.database.windows.net -U Daniel -n –q -F10 -L20This will export the data from the 10th row to the 20th row of the “dbo.dimdate” table and save it in the specified file.
Exporting Queries to a File
BCP also allows you to work with queries and export the results to a file. Here’s an example:
bcp "SELECT [EnglishMonthName],[FiscalYear],[FiscalSemester] FROM dbo.dimdate" queryout c:\sql\firstten.dat -d sqlcentralwarehouse -S sqlcentralserver.database.windows.net -U Daniel -n –qIn this example, we are running a query on the “dbo.dimdate” table in Azure and exporting the results to the specified file.
Working with Batches in BCP
When dealing with a large amount of data, you can use BCP to export or import data in batches. The “-b” parameter is used to specify the batch size. Here’s an example:
bcp "SELECT [EnglishMonthName],[FiscalYear],[FiscalSemester] FROM dbo.dimdate" queryout c:\sql\firstten.dat -d sqlcentralwarehouse -S sqlcentralserver.database.windows.net -U Daniel -n –q -b100In this example, we are exporting the query results in batches of 100 rows. This can improve performance and reduce memory usage when dealing with large datasets.
Error Files in BCP
BCP allows you to create error files that capture any rows that could not be transferred during the import or export operation. You can use the “-e” parameter followed by the path to specify the error file. Here’s an example:
bcp "SELECT [EnglishMonthName],[FiscalYear],[FiscalSemester] FROM dbo.dimdate" queryout c:\sql\firstten.dat -d sqlcentralwarehouse -S sqlcentralserver.database.windows.net -U Daniel -n –q -b100 -e c:\sql\error.logIn this example, any rows that encounter errors during the BCP operation will be logged in the specified error file.
Importing from a File to an Azure Table
BCP can also be used to import data from a file into an Azure table. First, you need to create a table in Azure. Here’s an example of creating a table using the Query Editor:
CREATE TABLE [dbo].[ImpDate]
(
[EnglishMonthName] [nvarchar](10) NOT NULL,
[FiscalYear] [smallint] NOT NULL,
[FiscalSemester] [tinyint] NOT NULL
)Once the table is created, you can use BCP to import data from a file. Here’s an example:
bcp sqlcentralwarehouse.dbo.ImpDate in c:\sql\firstten.dat -n -U daniel -S sqlcentralserver.database.windows.net -qIn this example, we are importing the data from the “firstten.dat” file into the “dbo.ImpDate” table in Azure.
Conclusion
BCP is a powerful tool for importing and exporting data in SQL Server. While it may not be the most intuitive tool for beginners, it offers great performance and flexibility for experienced users. Whether you need to transfer data between SQL Server and files or other databases, BCP is a reliable choice.
References: