How to Export a CSV File From a T-SQL Query

Knowing SQL queries to select data from databases allows you to obtain information easily. However, there are many situations in which we need to export data to another platform or application. The CSV file is a solution to this problem.

If you'd like to master complete SQL, try out our interactive SQL from A to Z track. It contains 7 hands-on SQL courses that will take you from a beginner to an advanced SQL user. Sign up now for free!

In this article, you'll find out what a CSV file is and why you would want to use it. You'll also learn 5 methods for exporting data to a CSV file from an SQL Server database. Let’s get started!

What Is a CSV File, and Why Is It Used?

You are probably wondering why you would want to export data to a CSV file. First, the CSV file (.csv) is a very popular data format, accepted by many applications like Excel, Calc, various IDEs, and database servers. In addition, it has a very simple structure, because the characteristic features are values in rows separated by commas.

The first line very often contains the row header. Look at the following sample file:

As you can see, the first line contains the header with the column names: id, first_name, and last_name. The rows that follow contain values corresponding to the column headers, separated by commas.

The popularity and the wide acceptance of this format allow for the exchange of data between different platforms and applications. You can export data from one database and import them into other software like Excel.

This independence also allows moving data between different operating systems like Windows and Linux. A lot of programs have an option to export and import from/to CSV files for this reason. Companies working with data on different platforms can operate on the same data and move data by using the CSV format to import and export.

The CSV format also provides an easy way to understand data because it is human-readable. The content of the CSV file can be seen as a spreadsheet in Excel or Calc.

The Example Data to Export

Let’s say we have this one table product in the SQL Server database:

As you know, we can get all rows from this table by using a simple SQL query:

SELECT * FROM product;

When we run it, all data is displayed in the SQL output window. But what if we want to see the same data in other applications like Excel? Read on to find out several methods for exporting data from a table to a CSV file.

The Methods for Exporting SQL Data From a Table to a CSV File

Now, we'll see how to export data from a query against an SQL Server to a CSV file. There are several different methods to do so.

Method 1: Using the SQL Server Management Studio

This method leverages the SSMS-GUI tool designed to manage objects and data in SQL Server.

First, run SSMS. In the Windows operating system, go to “Start”, expand the “Microsoft SQL Server Tools”, and select “Management Studio”.

In the next step, expand the Database node and right-click on your database that contains the table product (our database is store). From the menu, choose “Tasks” then “Export data”.

The Import and Export Wizard will be displayed:

Click on the “Next” button. From the “Data options” list, choose “SQL Server Native Client”:

Fill other fields like the name of the server, login, and password to the server, and choose the database storing the table you would like to export (our table product is in the database store):

Go to the next step by clicking the “Next” button.

Now, choose the destination file format. For a CSV file, choose “Flat File”. Fill the fields with the name of the file, the location, and the code page. Check the “Unicode” and “Column names” checkboxes:

In the next step, select either the option to transfer all data from the table or write an SQL query to do so. Going with the first option looks like this:

Select the table product from the table list:

In the next step, click the “Next” button.

And click the “Finish” button in the last step:

You see the information about successes and errors:

Close the wizard window and go to the file with the data from the table product. You can find it in the catalogs in the user folder of the Windows OS.

Method 2: Operating on a Result Set in SSMS – Data Without Column Names

Another method using SSMS for exporting data to a CSV file is based on the rows from a result set.

In the Object Explorer window, expand the Database node and select the database that contains our table product. Next, click the “New Query” button to open the editor for writing SQL queries. Select all data from the table product:

SELECT * FROM product;

Now, click the “Execute” button to run the query. In the result set, you see all data from the table.

In the next step, right-click on data rows in the result set and choose “Save Result As” from the menu.

In the File window, select the destination folder for your CSV file, enter the name of the file in the File Name field – “product-store” for example – and click OK. This file contains your data, but without the column names.

Method 3: Operating on a Result Set in SSMS – Data With Header

This method is similar to the previous method. It also is based on SSMS and the result set, but the differences are in the choices you make from the menu.

First, right-click on the result set and choose “Select All” from the menu.

It will select all rows.

Right-click on the selected rows and choose “Copy with Headers” from the menu. Go to an empty CSV file and simply paste the content.

Method 4: Using the SQLCMD Utility

SQLCMD is a tool that allows you to use a terminal in an operating system like Windows to run SQL statements or scripts. We can use it to export data to a CSV file.

First, open cmd in your OS and write the following command:

sqlcmd -S DESKTOP-INGEKE8\MSSQLSERVER,1433
-Q "select * from store.dbo.product"
–s "," –o "C:\Users\dora\Documents\products_list.csv" -E

Execute it by pressing “Enter”.

Let me explain this more in detail.

The first word is the name of the tool, sqlcmd. Next are the options (specific letters, each preceded by a dash) needed to export data from an SQL query to a CSV file.

The first option is -S, followed by the name of the server, a backslash, the name of the SQL Server instance, a comma, and the port number for the connection. In my case, SQL Server is on a machine called DESKTOP-INGEKE8, and the instance is MSSQLSERVER. Note that the default port is 1433.

The second option is -Q. This is followed by a SELECT statement to get the data from the table. Enclose the SELECT statement in quotes. In our example, store is the database, dbo is the default schema, and product is the name of the table.

The third option is -s followed by a comma in quotation marks. This specifies the comma as the separator between the columns in the CSV file.

The fourth option is -o to specify the path and the name of the output file. Enclose the path and the file name in quotation marks.

And the last option is -E. This indicates that it is a trusted connection to the database server.

The result is a CSV file (products_list.csv) containing the data from the table product of the database store at the path you specify.

bcp store.dbo.product out C:\Users\dora\Desktop\product-data.csv -S DESKTOP-INGEKE8\SQLEXPRESS -c -t"," -T
bcp "SELECT * from product WITH (NOLOCK)" queryout c:\product-store.csv -c -T

Method 5: Using the BCP Tool

Using the BCP tool is similar to the previous method. BCP is a command-line program that copies data from/to an SQL instance and from/to a data file, using a specified file format. You can use this tool in the terminal of your operating system.

Open cmd in the Windows OS and run the following:

bcp store.dbo.product out C:\Users\dora\Desktop\product-data.csv
-S DESKTOP-INGEKE8\MSSQLSERVER,1433 -c -t"," -T

The first word is bcp, followed by the names of the database, the schema, and the table, separated by the period. Then, the command out specifies the path to the result file. In my case, it is C:\Users\dora\Desktop\product-data.csv.

Next is the option -S, followed by the name of the server, a backslash, the name of the SQL Server instance, a comma, and the port number for the connection. In my case, SQL Server is on a machine called DESKTOP-INGEKE8, and the instance is MSSQLSERVER. Note that the default port is 1433.

The option -t (in lowercase) defines the separator enclosed in quotation marks. Here, the separator is the comma.

The option -T (in uppercase) stands for Trusted Windows authentication.

When you run this, you see something like the below to let you know the status of copying your data:

Let’s Export an SQL Table to a CSV File!

In this article, you have learned what a CSV file is, how this format is built, and why it is used in applications or platforms. You have also seen several methods for exporting data from a table in a Microsoft SQL Server database to a CSV file.

You might be interested to read about how to export to a CSV file from a PostgreSQL database in the article “How to Import CSVs to PostgreSQL Using PgAdmin”. Or learn more on SQL Server with courses on our platform LearnSQL.com.