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.