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.
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
The output:
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
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.
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.
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 ...
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.
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.