SQLServerCentral Article

Azure DWH part 16: BCP to import and export data

,

Introduction

BCP is a Bulk Copy Program used to import data from SQL Server to a file or export from a file to an Azure DW. In this article, we will show how to export data from Azure SQL Data Warehouse (ASDW) to a file or how to import data in an ASDW table.

Requirements

  1. A local machine with BCP 13.1 or later installed. There is a BCP version for Linux, however, we will focus on BCP for Windows which is similar to the Linux version, but not identical.
  2. We will also need an ASDW, with the Adventureworks LT database. For more information to create the database, you can check our article related.

Getting started

We will first check to the BCP version. You can use the following command in the cmd:

bcp -v

The version 13.x belongs to SQL Server 2016 and 12.x the version 2014. For more information about the versions, you can check the following link: How to determine the version, edition and update level of SQL Server and its components

The next example will show how to copy the Azure table information to a data file:

bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\dates.dat -S sqlcentralserver.database.windows.net -U Daniel -P MySecretpwd012 -n –q

The example copies the dbo.dimdate table information to a file named dates.dat -S is the Azure Server name. You can get the server name from the Azure Portal.  To get that information, in the Azure Portal, select More Services and look for SQL servers:

Select your Azure SQL Server and go to Properties. You will find the Azure server name there:

If you did not enable before, you may need also to go to the Firewall/Virtual Networks section and Add your client IP. Do not forget to save the information:

Sqlcentralwarehouse is the name of the SQL database. You can find the name when you click the Databases icon.  In properties, you will find the User name which is daniel. You use -U to specify the user name to login:

-P is to specify the password, -n is used to perform operation in native format and -q is used to handle quoted identifiers.

If everything is OK, you will have a file named dates.dat in your local machine:

BCP with prompt password

In the previous example, you can see the password in a plain text. Sometimes to automate, there is no other option. However, there are options to manually specify the password:

bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\dates.dat -S sqlcentralserver.database.windows.net -U Daniel -n –q

This example is equal to the previous one, but now we are not specifying the password (-P) parameter.

Hints in BCP

The  -h (you need to be very careful with uppercased or lowercased parameters with BCP) is used to apply hints. For example, you can order by alphabetical order using the -h with ORDER to specify the order of a specified column or you can specify the rows per batch or lock the table while the import or export operation is done.

The following example will lock the table while the BCP operation is done. This hint (-h TABLOCK) increases the performance when the table is big and there are several users accessing the table. With this hint multiple concurrent users can load the table:

bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\dates.dat -S sqlcentralserver.database.windows.net -U Daniel -n -q -h TABLOCK

Show the first 10 rows in BCP

The next example shows how to show all the columns until the row 10. With the -L parameter, you can specify your last row to display. The following example export the table data until the row 10:

bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\firstten.dat -S sqlcentralserver.database.windows.net -U Daniel -n –q -L10

Export specified rows

In BCP you can export from row x to row y. You use the First (-F) and Last (-L) parameters for this. The following example will export to a file the row 10 until the row 20:

bcp sqlcentralwarehouse.dbo.dimdate out c:\sql\firstten.dat -S sqlcentralserver.database.windows.net -U Daniel -n –q -F10 -L20

Export queries to a file

You can also work with queries in Azure and export them to a file using BCP. The following example will run a query to the dbo.dimdate table in Azure to a .dat file:

bcp "SELECT [EnglishMonthName],[FiscalYear],[FiscalSemester] FROM dbo.dimdate" queryout c:\sql\firstten.dat -d sqlcentralwarehouse -S sqlcentralserver.database.windows.net -U Daniel -n –q 
We use the queryout to handle queries. 

Working with batches in BCP

When the data to export is a lot, you can bulk copy using batches. The batches parameter (-b) is used. In the following example, we will export the Azure query to a dat file in batches of 100 rows:

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

Error files in BCP

You can create a file with the rows that could not be transferred. We use the -e parameter for this followed by the path:

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.log

Import from a file to an Azure table

We can also import from a file to a table.

To do this, we will create a table first in Azure, to databases and select the ASDW. In Overview, select Query editor:

Login and create a table:

CREATE TABLE [dbo].[ImpDate]
(
[EnglishMonthName] [nvarchar](10) NOT NULL,
[FiscalYear] [smallint] NOT NULL,
[FiscalSemester] [tinyint] NOT NULL
)

In your local machine, in the cmd, run the following commands:

bcp sqlcentralwarehouse.dbo.ImpDate in c:\sql\firstten.dat -n -U daniel -S sqlcentralserver.database.windows.net -q

In this example, we are importing in our Azure table dbo.ImpDate the values of the firstten.dat file (created in previous examples). 

Conclusion

BCP is a useful tool to automatically import and export data. For UI users, it is not an intuitive tool, but for experimented users it is simple and very fast. One of the best tools to import when we talk about performance.

References

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating