blog.atwork.at

news and infos about microsoft, technology, cloud and more

Import and export data to an Azure SQL Database

BCP, the bulk copy program utility is still around - and it’s helpful! It’s a long time, since I used this tool, but nowadays it got promoted for one of my daily jobs. See a short description how to use the Microsoft Command Line Utilities with Azure SQL Database for connecting and importing and exporting data quickly.

Recently, I had the request to import local data to a SQL Azure database and BCP did the job. Since the tool is handy and very fast, it’s also good for running in a script (but you need to find a solution for storing the credentials…). So, here’s an overview how to use BCP and SQLCMD as quickly as possible.

The command line tool BCP can be downloaded from the Microsoft Command Line Utilities 13.1 for SQL Server page and installed on a Windows machine (see requirements and note that this component requires both Windows Installer 4.5 and Microsoft ODBC Driver 13.1 for SQL Server).

The basic syntax

Then, the bcp utility shows it’s parameters. Basically, it knows how to import or to extract data, to query data an dot use a format file with data.

image

The SQL Server user must have access to the database and must be added (you could use environment variables as well). So, here’s the most common, basic syntax for exporting data from SQL Azure.

bcp [database].[Schema].[Table] out [filename] -c -t, -S tcp:[servername].database.windows.net,1433 –U [username] –P [password]

See the full description at https://docs.microsoft.com/en-us/sql/tools/bcp-utility.

Export data

Exporting data is easy with the out parameter.

bcp [database].[Schema].[Table] out [filename] -c -t, –S …

I have included –c for not prompting for each field data type and –t, for using a comma as separator (there is not space between -t and the comma). With Azure, the servername is always the full path to the database.windows.net domain. If any database object has spaces or a dot included in its name, use [ ], as for example: bcp [atwork.at].dbo.myTable …

Example: bcp atworkblog.dbo.be_Categories out c:\temp\be_Categories.csv -c -t,
-S tcp:mydbserver1.database.windows.net,1433–U mysqluser –P mysqlpwd

image

The output:

image

Queries

Theoretically, BCP can run a SQL query as well with the queryout parameter…

bcp "SELECT * FROM atworkblog.dbo.be_Categories" queryout c:\temp\be_Categories.csv
-S tcp:[servername].database.windows.net,1433 –U [username] –P [password]

…but unfortunately, the execution of a query works just with local databases. Obviously it does not work with SQL Azure, see the following error result:
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Reference to database and/or server name in 'myDB.dbo.myTable' is not supported in this version of SQL Server.

Again, locally this works perfectly. I just had the idea to use a view instead… anyway, if anyone has a solution for that with SQL Azure, pls. ping me (see also here)…

Import data

Importing needs a local text file and the in parameter. The target table mytable must be already existing and the columns must match.

Example: bcp atworkblog.dbo.mytable in c:\temp\mytable.csv -c -t,
-S tcp:mydbserver1.database.windows.net,1433–U mysqluser –P mysqlpwd

image

You will see the imported data in that table.

Specify the file format

For modifying the file format, check out Use a Format File to Bulk Import Data (SQL Server). First, you need to create a format file with the format nul parameter as here:

bcp atworkblog.dbo.be_Categories format nul -c –t; -f c:\temp\be_Categoriesformat.xml –S …

Here, we use a semicolon as separator instead of a comma, specified with –t; . This command creates the format file.

image

Then, you can use that format file for further operations. Here, we export data with that format file (don’t be confused with the file extension .xml, that’s just random because my fingers typed that automatically…).

bcp atworkblog.dbo.be_Categories out c:\temp\be_Categories.csv -f c:\temp\be_Categoriesformat.xml –S …

As we can see, the new separator in the generated data file now is the semicolon.

image

The file format can also be used for importing data and even allows mappings, skipping columns, etc, see the documentation for that. You can use XML format as well. In that case, the format file would be generated with the –x parameter.

bcp atworkblog.dbo.be_Categories format nul -c –x -f c:\temp\be_Categoriesformat.xml –S …

image

Then, repeat the out or in command with that file again.

Use SQLCMD

Another helpful tool included in the Microsoft Command Line Utilities 13.1 for SQL Server is the SQLCMD command. This allows to connect directly to a SQL Server and to execute commands. Thy syntax is very similar. Connect to a SQL Azure database as here:

sqlcmd -S tcp:[servername].database.windows.net,1433 –d [database] –U [username] –P [password]

Now you can run TSQL commands like in the screenshot.

image

Use exit to quit the session.

Helpful tools

BCP and SQLCMD are helpful command line tools in the Windows world to communicate with a SQL Server or SQL Azure. For me, it was a nice reminder that these utilities can support for small database tasks in daily life. I hope, this short summary helps other Admins as well.

Loading