SQL Server: Export Table to CSV

Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

Note: To handle complex scenarios and large files, use a utility like DTS/SSIS (SQL Server Integration Services). However for simpler scenarios, the approach shown in this article works fine.

Create a test table

CREATE TABLE test(
empid varchar(6),
empname varchar(100),
dob datetime,
salary decimal(12,2)
)

Consider the following data

INSERT INTO test
SELECT 'EMP001','Suresh','19910619',3000
UNION ALL
SELECT 'EMP002','Ramesh','19710103',20000
UNION ALL
SELECT 'EMP003','Nilesh','19800722',4760
UNION ALL
SELECT 'EMP004','Kumar','19680911',42000

Method 1: Use bcp in command prompt

Usually when we use bcp to export data, by default column values are separated by a tab.
So we need to use a format file to make the column delimiter a comma instead of a tab.

Create a file named format.fmt in F drive (or whichever drive is available in your system)
with the following data. You can also download format.fmt over here

Over here, 9.0 refers to the SQL Server Edition (in this case SQL Server 2005) and 4 represents the number of rows (in this case 4 rows).

Now open the command prompt and use the following bcp command in it

bcp yourdb..test out D:\test.csv -T -f D:\format.fmt

The file named F:\test.csv will be created with values separated by comma.

Method 2: Use Management studio

Right click on the database and select Tasks > Export data

Select Data source as SQL Server, select the server name, authentication and database and click Next

Select Destination as Flat file Destination and browse for a .csv file and click Next

Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next

At the end it will show the details of the rows which got exported to .csv file.

Note: If you intend to do the reverse, i.e. Import .csv into a table, check my previous article Load Comma Delimited file (csv) in SQL Server

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan