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.

8 Answers 8


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



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


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.

  • 4
    +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
    Commented Sep 3, 2012 at 9:30
  • Any answer involving a GUI program is simply out of scope. This is the best solution from a DBA point of view!
    – EnzoR
    Commented Mar 9, 2020 at 9:28

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.

  • 1
    Note that "Save result as" doesn't save the headers by default. To add the columns as well, go to Query > Query Options > Results > Grid > check "include column headers when copying or saving the results", as well as "Quote strings containing list separators when saving .csv results" (see also this answer)
    – HeyJude
    Commented Aug 10, 2021 at 12:41


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.
  • 1
    One more caveat: you may need permissions, otherwise you will see: Msg 15281, Level 16, State 1, Line 2 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online. Commented Jan 28, 2021 at 3:19

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='[email protected]',
    @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.

  • 1
    Just what I was looking for, kudos to you!
    – Mashchax
    Commented Apr 1, 2019 at 10:40
  • 1
    Although you will need to add @profile_name parameter if you have no default private profile for your user nor public profile for the database.
    – Mashchax
    Commented Apr 1, 2019 at 10:48

Low tech, but...

   col1 + ','
+  cast(col2 as varchar(10)) + ','
+  col3
from mytable;
  • MySQL treats col1 + ',' + cast(col2 as varchar(10)) + ',' + col3 as a column name, and prints NULL all over the place. Commented Oct 24, 2019 at 12:03

This can easily be done in a 3 step process:

  1. Write your query as a SELECT INTO query. This will essentially export your query results into a table.

    INTO dbo.LogTableName         --Table where the query Results get logged into.
    From Table

    Note, this table cannot exist when the query runs, as this type of query wants to create the table as part of the execution.

  2. Then use SSIS to export the results of that table to .csv. The SSIS Export wizard lets you pick your delimiter, etc. This is really handy for using a | delimiter in instances where the result set contains commas.

    Right click on DB Name > Tasks > Export Data

  3. Once the Export is complete, execute the DROP TABLE command to cleanup your log table.

    Drop Table dbo.LogTableName
  • 3
    Hi Don. I added some formatting to your answer. You can click on 'edit' in the lower-left to see what's changed and click on the "edited" xx minutes" bit to see a full history if you want to roll back. Although workable, your answer doesn't add much over what's already been written and is unlikely to attract much attention. Commented Nov 8, 2016 at 22:14

I use MS Access DoCMD to export CSV's from SQL Server. It is able to do it with a single command. The access database is only used to enable the access command. It performs well, and as an added benefit (not sure why) it creates a schema.ini for the table exported.


CMD="SELECT * INTO [Text;HDR=Yes;DATABASE=C:\].[Results.txt]  
FROM [ODBC;DSN=SQL2017;Description=SQL2017;UID=.;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=LAPTOP-XYZ;DATABASE=TempDB_DataDump_1].Results "

 Set appAccess = CreateObject("Access.Application") 
                appAccess.OpenCurrentDatabase "anyAccessdatabase.mdb" 
                   ' appAccess.DoCmd.SetWarnings 0
                    appAccess.DoCmd.runsql "CMD"

This procedure will generate a CSV file with the output of the query you pass as a parameter. The 2nd parameter is your CSV file name, make sure you pass the file name with a valid writable path.

Here is the script to create the procedure:

CREATE procedure [dbo].[usp_create_csv_file_from_query]
    @sql Nvarchar(MAX),
    @csvfile Nvarchar(200)
        if IsNull(@sql,'') = '' or IsNull(@csvfile,'') = ''
            RAISERROR ('Invalid SQL/CsvFile values passed!; Aborting...', 0, 1) WITH NOWAIT
        DROP TABLE if exists global_temp_CSVtable;
            @columnList varchar(4000), 
            @columnList1 varchar(4000),
            @sqlcmd varchar(4000),
            @dos_cmd nvarchar(4000)

        set @sqlcmd = replace(@sql, 'from', 'into global_temp_CSVtable from')
        EXECUTE (@sqlcmd); 

        declare @cols table (i int identity, colname varchar(100))
        insert into @cols
        select column_name
        from information_schema.COLUMNS
        where TABLE_NAME = 'global_temp_CSVtable'

        declare @i int, @maxi int
        select @i = 1, @maxi = MAX(i) from @cols

        while(@i <= @maxi)
            select @sql = 'alter table global_temp_CSVtable alter column [' + colname + '] VARCHAR(max) NULL'
            from @cols
            where i = @i
            exec sp_executesql @sql
            select @i = @i + 1

             @columnList = COALESCE(@columnList + ''', ''', '') + column_name 
            ,@columnList1 = COALESCE(@columnList1 + ', ', '') + column_name
        from information_schema.columns
        where table_name =  'global_temp_CSVtable'

        SELECT @columnList = '''' + @columnList + '''';

        SELECT @dos_cmd='BCP "SELECT ' + @columnList + 
                        ' UNION ALL ' +
                        'SELECT * from ' + db_name() + '..global_temp_CSVtable" ' + 
                        'QUERYOUT ' + @csvfile + 
                        ' -c -t, -T'
        exec master.dbo.xp_cmdshell @dos_cmd, No_output 
        DROP TABLE if exists global_temp_CSVtable;
  • 2
    code only answers aren't seen as acceptable here - could you add some commentary on your answer and why it works? (code comments would probably help here too) Commented Apr 5, 2019 at 13:32
  • Yes, and could you also add an example as how you successfully use it? Commented Sep 28, 2019 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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