I Need Best Practice In T-sql Export Data To Csv (with Header)

July 25, 2024 Post a Comment

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 t

Solution 1:

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

SELECT'object_id', 'name'UNIONALLSELECT 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:

INSERTINTO 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:

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.
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

Lab Sql