Export Data From SQL to CSV

When there is a discussion about exporting data from SQL Server to CSV file, we immediately look for the Integration Services. But, you can achieve the same result from the Management Studio with ease. In this article, we will show you multiple approaches to export data from SQL to CSV with a practical example.

For the first Export Data From SQL to CSV example, we are going to use the below-shown records.

Employee Table data 1

Export Data From SQL to CSV Approach 1

In this example, we are going to export the Employees table present in the SQL tutorial database to a CSV file (that we will create) on the local hard drive. For this, please select all the columns, either clicking the top left corner or selecting the Select All option from the context menu.

Copy the Result set in management Studio 2

After selecting all the columns, please go to the top left corner, and right-clicking there will open the context menu. Please select the Save Result As.. from it.

Choose Save the management studio result set as 3

That will open a Save Dialog Box to save the file on your local hard drive. From the below screenshot you can see that we are saving the file in D drive.

Export Data From SQL to CSV 4

Let’s navigate to the file system address that we provided and check for the file name EmployeeData.

View CV File in the local hard drive 5

Double click on CSV file to check the result

Saved Data in CSV File 6

Export Data From SQL to CSV Approach 2

Within the Object Explorer, right-click on the database will open the context menu. Please select the Tasks and then Export Data.. option from it to Export Data From SQL to CSV.

Export Data From SQL to CSV 7

Once you select the option, It will open the Import and Export Data Wizard. The first page is the welcome page, and you can avoid this page by clicking the Do not show this Starting page again option.

Import and Export Wizard Welcome Page 8

Choose a Data Source Page: This window is used to configure the source from where the records is coming. Our source is the SQL Server database, so we selected the Native Client as Data Source.

Choose SQL Server Native Client as the Data Source connection 9

Next, we are using Windows authentication to log in to the PRASAD server instance. Please use Server Authentication in real-time.

Export Data From SQL to CSV 10

Choose a Destination: This window is used to configure the destination (to where we want to copy this).

Choose the Destination 11

Our target is to save the data into a CSV file. So we are selecting Flat File Destination as our Destination.

Choose Flat File Destination 12

Please click on the Browse button to choose the CSV file from our local drive.

Export Data From SQL to CSV 13

We are creating a new CSV in our D drive.

choose the CSV format and click save button 14

Click on the Open button to create a new CSV file.

Choose a CSV from the System Hard drive 15

Next, select the Format that you want to assign.

Export Data From SQL to CSV 16

Specify Table Copy or Query: Here, we have two options to Export Data From SQL to CSV.

  1. Copy data from one or more tables or views: This option is to select from existing tables or views (All the columns)
  2. Write a query to specify file data to transfer options: In real-time, we are going to use this option. Because there will be unnecessary columns in every table. So it is better to avoid them or select data with the condition.
Export Data From SQL to CSV 17

This window is used to select the source table from the database. Here we are selecting the Employees table.

Select the Source Table or View 18

You can change the Row and Column delimiters as per your requirements. If you are using a text file as the destination, then you can do many customizations. Click on the preview button to check the incoming data flow

Configure Flat File Destination Row and Column Delimiter 19

Save and Run Package: This page gives us the option to save the package in either an SQL server or a File system. For the time being, I am selecting the Run option immediately.

Save and Run the Package 20

Click Finish button to complete the Export Data From SQL to CSV Wizard

Export Data From SQL to CSV 21

From the below screenshot, you can observe the status: Success and Message.

Export Data From SQL to CSV 22