How can I solve Writing select result to a csv file?

Question

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.I tried to use the suggested proc in the article below, but when I run the proc, SQL complains that can't run sys.sp_OACreate and sys.sp_OADestroy which are called in this proc.

Do you know how we can turn on these components or know any better way to write to a file using T-SQL? Thanks in advance.

Anu Rhea · Accepted Answer

Use BCP utility to solve SQL server export to CSV bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:MyTable.csv" -c -t , -S SERVERNAME -T The -c argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but -t , changes the field terminator to commas. -T specifies Windows authentication ("trusted connection"), otherwise use -U MyUserName -P MyPassword. This doesn't export column headers by default. You need to use a UNION ALL for headersOR Use SQLCMD SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:MyData.csv" Hope this code helps you to solve sql server export to csv!

How can I solve Writing select result to a csv file?

About this question

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

SQL Server Tutorials

SQL Server Interview Q&A

Latest SQL Server Blogs

Want answers to more SQL Server questions?

Stay in the loop — it’s free

You’re on the list!