Export table from database to csv file

Question

I want to: Export table from sql server database to a comma delimited csv file without using sql Server import export wizard

I want to do it using a query because I want to use the query in automation

Is it possible? I searched for that and didn't find a good answer

Answer 1

Some ideas:

From SQL Server Management Studio

 1. Run a SELECT statement to filter your data
 2. Click on the top-left corner to select all rows
 3. Right-click to copy all the selected
 4. Paste the copied content on Microsoft Excel
 5. Save as CSV

Using SQLCMD (Command Prompt)

Example:

From the command prompt, you can run the query and export it to a file:

sqlcmd -S . -d DatabaseName -E -s, -W -Q "SELECT * FROM TableName" > C:\Test.csv

Do not quote separator use just -s, and not quotes -s',' unless you want to set quote as separator.

More information here: ExcelSQLServer

Notes:

• This approach will have the "Rows affected" information in the bottom of the file, but you can get rid of this by using the "SET NOCOUNT ON" in the query itself.

• You may run a stored procedure instead of the actual query (e.g. "EXEC Database.dbo.StoredProcedure")

• You can use any programming language or even a batch file to automate this

Using BCP (Command Prompt)

Example:

bcp "SELECT * FROM Database.dbo.Table" queryout C:\Test.csv -c -t',' -T -S .\SQLEXPRESS

It is important to quote the comma separator as -t',' vs just -t,

More information here: bcp Utility

Notes:

• As per when using SQLCMD, you can run stored procedures instead of the actual queries
• You can use any programming language or a batch file to automate this

Hope this helps.