When there is a discussion about exporting data from SQL Server to CSV file, we immediately look for the SQL Server Integration Services. But, you can achieve the same result from the SQL Server Management Studio with ease. In this article we will show you multiple approaches to export data from SQL to CSV with practical example.
For the first example, we are going to use below shown data.
Export Data From SQL to CSV – Approach 1
In this example, we are going to export Employees table data present in the SQL tutorial database to csv file (that we will create) in the local hard drive. In order to so, please select all the columns either clicking the top left corner, or selecting Select All option from the context menu.
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 the context menu.
That will open a Save Dialog Box to save the file in your local hard drive. From the below screenshot you can see that, we are saving the file in D drive
Let’s navigate to file system address that we provided, and check for the file name EmployeeData.
Double click on csv file to check the result
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 the context menu as we shown below.
Once you select the Export Data.., It will open SQL Server Import and Export Data Wizard. First page is welcome page, and you can avoid this pag by clicking the Do not show this Starting page again option
Choose a Data Source Page: This window is used to configure the source from which the data is coming. Our source is SQL Server database so we are selecting SQL Server Native Client as Data Source.
Next, we are using the Windows authentication to login to PRASAD server instance. Please use SQL Server Authentication in real-time.
Choose a Destination: This window is used to configure the destination (to where we want to copy this data).
Our target is to save the data into CSV file so we are selecting Flat File Destination as our Destination
Please click on the Browse button to select the csv file from our local drive
We are creating new CSV in our D drive
Click on the Open button to create a new csv file.
Next, Select the Format that you want to assign.
Specify Table Copy or Query: Here we have two options
- Copy data from one or more tables or views: This option is to select from existing tables or view (All the columns data)
- 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 selecting data with condition.
This window is used to select the source table from SQL database. Here we are selecting Employees table.
You can change the Row and COlumn delimiters as per your requirements. If you are using text file as the destination then you can do many customizations. Click on preview button to check the incoming data flow
Save and Run Package: This page gives us the option to save the package in either SQL server or File system. For the time being I am selecting Run immediately option
Click finish button to complete the Wizard
From the below screenshot you can observe the status: Success and Message.
Thank You for Visiting Our Blog