Within SQL Server, there is an application called Bulk Copy Program (BCP) which is generally used for importing data very quickly into SQL Server. However, the application also has the functionality to export data from a table or query into an external file.
Below is the command to export all of the data from Northwind.dbo.Customers into a NorthwindCustomers.csv file. I then use the -t, to say the column separator should be a comma.
“C:Program FilesMicrosoft SQL Server90ToolsBinnbcp.exe” Northwind.dbo.Customers out “C:NorthwindCustomers.csv” -c -T -t,
If we wanted some more control, we could write a SQL query and use the queryout option to export the results returned.
“C:Program FilesMicrosoft SQL Server90ToolsBinnbcp.exe” “SELECT * FROM Northwind.dbo.Customers” queryout “C:NorthwindCustomers.csv” -c -T -t,
Combine this with xp_cmdshell, and all of it can be done from a stored procedure. Great way for getting data very quickly out of a table and into a flat file.
Thanks a lot, this is very helpful. One thing that I notice is that this is not true CSV, it’s just a comma delimited file with a CSV extension. I think true CSV will wrap each field in double quotes and escape any double quote in a column with another double quote. Can BCP do that?
Thanks again!
Unfortunately, BCP does not feature a “double quote” parameter. You’ll need to create a view with the headers of your table and then UNION ALL with the data, wrap the headers and data in double quotes, and then export the view.
I discovered this workaround when trying to solve the problem myself. Hope that helps!