Azure Synapse Export Data using sqlcmd – Example

  • Post author:
  • Post last modified:March 9, 2021
  • Post category:Azure Synapse
  • Reading time:6 mins read

Exporting data to CSV or text format is a common requirement in the data warehouse. Many application accept delimited data as an input. Instead of providing access to actual tables, organizations export data in the form of CSV and provide it for analysis. In this article, we will check how to export Azure Synapse data using sqlcmd command line interface. We will also check different method that you can use sqlcmd to export data.

Azure Synapse Export Data using sqlcmd

The sqlcmd command line tool is one of the easiest tools that you can use to export data. It is simple use and options are self explanatory.

Using sqlcmd you can export tables from Azure Synapse to a delimited or text file in two ways.

  • By using SQLCMD on Command Prompt
  • By selecting SQLCMD Mode in SSMS

Now let us check these two methods in brief.

Export Azure Synapse Data using SQLCMD on Command Prompt

The sqlcmd utility lets you submit Transact-SQL statements such INSERT, UPDATE, DELETE, system procedures, and script files to Azure Synapse data warehouse server at the command line.

You can download and install sqlcmd from official location. Alternatively, this utility will be installed when you install SQL Server Management Studio (SSMS).

Following is the example of using sqlcmd utility to export Azure Synapse table or query output.

sqlcmd -S azuresynapse.database.windows.net -d dev -U azureuser -P Password@123 -I -h -1 -W -Q "set nocount on;select * from output_test;" -s ',' -o D:\tmp\test_output.csv

Note that, we are executing set nocount on. This will remove the output message.

You get find more details about command line options in official documentation.

File will be available in the mentioned location if command is executed without any issue.

You can check using ‘more’ command. For example.

C:\Users\vithal>more D:\tmp\test_output.csv
4'def'104
6'fgh'106
2'bcd'102
1'abc'101
3'cde'103
5'efg'105

Export Azure Synapse Data using SQLCMD MODE in SSMS

This is another easy method that you can use to export data from Azure Synapse.

You have to enable SQLCMD MODE in SSMS. SQLCMD MODE is available under ‘Query‘ option.

For example,

Azure Synapse Export Data using sqlcmd - SQLCMD MODE

In the ‘Results To‘ option, select ‘Result to File‘ option and execute query to create the file.

There are various options that you can set in ‘Query Options‘.

For example,

Azure Synapse Export Data using sqlcmd - SQLCMD MODE Query Options.

Choose the appropriate options as per your requirements.

Also Read:

Hope this helps 🙂

Tags: ,