How to Export SQL Server Data to a CSV File?

Here we will see, how to export SQL Server Data to CSV file by using the 'Import and Export wizard' of SQL Server Management Studio (SSMS).

CSV (Comma-separated values): It is a file that consists of plain text data in which data is separated using comma(,). It is also known as Comma Delimited Files because comma act as a delimiter for CSV files.

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks.

Query:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek's database.

Query:

CREATE TABLE demo_table
(FIRSTNAME VARCHAR(20),
LASTNAMENAME VARCHAR(20),
AGE INT,GENDER VARCHAR(20));

Step 4: Insert data into the table

Query:

INSERT INTO demo_table VALUES
('Romy', 'Kumari', 22, 'female'),
('Sujata', 'Jha', 20, 'female'),
('Shalini', 'Jha', 22, 'female'),
('Ayushi', 'Chaudhary', 23, 'female'),
('Rinkle', 'Arora', 23, 'female'),
('Kajal', 'Sharma' ,24, 'female');

Step 5: See the content of the table

Use the below command to see the content of the demo_table:

Query:

SELECT * FROM demo_table;

Output:

Now we see steps to Export SQL Server Data into a CSV file.

Step 1: Open object Explorer from View option in the menu bar.

It appears on the left side of the screen. 

We can see the 'geeks' database inside the database folder.

Step 2: Right click on the geeks database, Click on Tasks-> Export Data. 

Step 3: Import and Export wizard will open, fill the details like server name, database etc. Then click on Next.

Step 4: Choose 'Flat File destination' as destination and choose file path. One can choose any text file as destination file. We have selected hello.txt as destination file to save the SQL Server data. Then Click Next

Step 5: choose table name and select comma as delimiter. then Click Next to proceed.

Step 6: No changes required in Save and Run package window. Click Next.

Step 7: On the "Complete" Wizard window, check all the settings set during of exporting process. Click "Finish" to start exporting SQL database to CSV.

Step 8: Below window shows the successful execution

Step 9: Check the destination file which is "hello" in this case.

We can see the content is same as the content of demo_table separated by comma.