I need best practice in T-SQL Export data to CSV (with header)

Question

What I need to do is export data into CSV file using T-SQL.

And I'm very confused about there are many ways can do it, I don't know to choose which one, please help me to confirm the bollowing:

As I know there are about 3 methods, and I want you help me to confirm:

Using Microsoft.Jet.OLEDB.4.0, like this:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                       'Text;Database=C:\Temp\;HDR=Yes;',
                       'SELECT * FROM test.csv')
            (object_id, name)
SELECT object_id, name
  FROM sys.tables;

but this need the csv file is there, and with header

using SQLCMD

command line.

using BCP

Use union, get data and it's column header.

This is all my understanding about T-SQL export to CSV, please help me to confirm.

Is there other way to export to CSV?

Thanks!

Solution 1

You could use a UNION to create a header row, like this:

SELECT 'object_id', 'name'

UNION ALL

SELECT object_id, name
FROM sys.tables

Solution 2

Execute the below command in SQL Server:

EXEC xp_cmdshell 'SQLCMD -S . -d MsVehicleReg2 -Q "SELECT * FROM tempViolationInfo" -s "," -o "O:\result.csv"';

Solution 3

Here is the T-SQL way:

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.

Solution 4

For Ace.OLEDB.12.0 (the new Jet redistributable engine), you can install the 32-bit or 64-bit stand-alone engine, even if you have the "other flavor" already installed, whether on its own, from Access, etc:

use the /passive command-line option:

(32-bit): AccessDatabaseEngine.exe /passive (64-bit): AccessDatabaseEngine_64.exe /passive

In my case, I have 64-bit SQL Express 2008 R2, and had 32-bit Office 12 apps installed (thus, the 32-bit ACE drivers were installed). I installed the 64-bit AccessDatabaseEngine_64.exe, and it's sort of working for me now...

Also, this is assuming you've done the other configuration work:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GO

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO

Solution 5

regarding "best practice", there is no best practice. there are several options available, not limited to:

• in T-SQL with INSERT INTO OPENROWSET(...) SELECT * from [MyTable]...

• executing BCP, whether from a job step or in T-SQL with xp_cmdshell

• SSIS packages

• PowerShell (from a job step, in SQL Server 2008+) or other external script/executable

View more solutions

Comments

[Guoliang]

Sorry, actually, I want a best practice to export data to CSV.

[Jim]

The best practice is subjective. Use the method that works for you. If you'd like the "most official" solution, that would be to use an SSIS package and schedule it with a SQL Server Agent job.

[Guoliang]

Thanks for your reply, using bcp and union can export to csv with header. thanks! Actually, we are moving out from DTS/SSIS...

[Jesse]

Welcome to Stack Overflow! I think you should explain what the command does. And it would be extremely useful if you detailed each of the commands you're suggesting; but you don't have to.

[Lee Richardson]

Could not find stored procedure 'xp_cmdshell' ?