There are many ways to export CSV file from Azure SQL Databases. This article shows two approaches: bcpand sqlcmdcommands.
Use bcp command
Command bcpfull name is bulk copy program and its a utility tool with SQL Server different versions. The tool is available on Azure Cloud Shell. We can use this tool to export simple CSV that doesn't require double quotes or other complex transformations.
Steps
Follow the steps below to use bcp command.
Log in to Azure portal.
Click Cloud Shell.
Choose Bash.
Once initialized, we can use a command like the following to export data:
bcp schema.object_name out ./data/file.csv -t "," -w -S server-name.database.windows.net -U username -d databaseReplace the bold parts accordingly.
The above command exports data from schema.object_name (can be view or table) to a local file named file.csv in datafolder. As password is not specified, the program will ask for password input interactively.
The following screenshot is one example:
Use SqlCmd command via PowerShell
If we want to be more flexible about the output CSV file format, we can use PowerShell Sqlcmd command.
Steps
Log in to Azure portal.
Click Cloud Shell.
Choose PowerShell.
Run Invoke-Sqlcmd command and then use Export-Csv to export.
Invoke-Sqlcmd -Query "select * from schema.object_name" -ServerInstance "server-name.database.windows.net" -Database database -Username username -Password "your-pwd" -IgnoreProviderContext | Export-Csv -path ./data/file.csv -Delimiter "," -Encoding UTF8
The above command performs similar action as the bcpcommand but with more extra features. For example, Export-Csv will double-quote all fields by default and we also specified the output file encoding too.
For more details about these two PowerShell commands, please find out more in References section.
Move to Azure Blob Storage
The exported files are located in Cloud Shell (Azure storage account file share image). If you want to move the files to other blob storage containers, simply use az storage command.
Refer to az storage | Microsoft Docs for more details about this command.