Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 16 down vote accepted

Use BCP utility

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 headers

OR

Use SQLCMD

SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable"
-s "," -o "D:\MyData.csv" 

OR

Use Powershell

Here is a link to an article. If you end up using this, you might want to append -notype after Export-Csv $extractFile to get rid of the unnecessary column on the output file.

share|improve this answer
1  
+1 For BCP utility - it is very easy for use, it is the fastest tool that I have ever used for export/import information and there are a lot of options. I will advice you to add "> FileName.log" at the end of the statement - this will create a log file. –  gotqn Sep 3 '12 at 9:30

Adding to the previous answer, which helps you automate the action, if you need it only from time to time you can do it in Management Studio, simply right click on the header - Save results as -> choose a .csv file.

Or if you want to do that for each select statement that you run, you can change the output direction of the results to a file. Use Tools -> Options -> Query Results - Results to file.

Another way, that can be automated easily, and makes use of SSIS, is by using Management Studio's Export Data feature. Right click on the database -> Tasks -> Export data. There is a wizard with lots of options there. You should choose the source database, the destination and other options. For the destination make sure it's "Flat file", browse and choose .csv type, choose whatever formatting you need and at the end you'll obtain an SSIS package that can be saved locally and repeated on need.

share|improve this answer
    
+1 for import/export wizard. I forgot that option. –  yogirk Sep 3 '12 at 15:24

Use T-SQL

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName

But, there's a couple of caveats:

  1. You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.
  2. The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.
share|improve this answer

Low tech, but...

select
   col1 + ','
+  cast(col2 as varchar(10)) + ','
+  col3
from mytable;
share|improve this answer

It looks like you already have an acceptable solution, but if you have db mail set up you could potentially do something like this:

EXEC msdb.dbo.sp_send_dbmail
    @recipients='your.email@domain.com',
    @subject='CSV Extract', 
    @body='See attachment',
    @query ='SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'CSV_Extract.txt',
    @query_result_separator = ',',
    @query_result_header = 1

If you are emailing the file somewhere anyway, it might save you a few steps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.