SQL Server如何通过查询自动定时导出数据至指定CSV文件？

Great question! Since you’re restricted to using only SQL Server queries (no SSIS, PowerShell, or cmdlets) to automate daily CSV exports—replacing MySQL’s INTO OUTFILE—here are two reliable approaches you can use, plus how to set up scheduling:

1. Use xp_cmdshell with the bcp Utility #

The bcp command-line tool is native to SQL Server, and you can invoke it directly from a T-SQL query using xp_cmdshell. This lets you export query results to a CSV, and you can dynamically generate date-stamped filenames.

Step 1: Enable xp_cmdshell (if not already enabled) #

First, you’ll need to turn on this advanced option (only do this if your security policies allow it):

-- Enable advanced options
sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Enable xp_cmdshell
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Step 2: Build and Execute the Export Query #

Use dynamic SQL to create a date-specific filename, then run the bcp command:

DECLARE @bcpCommand NVARCHAR(4000);
DECLARE @exportPath NVARCHAR(255) = 'C:\Your\Target\Directory\';
DECLARE @fileName NVARCHAR(255) = 'daily_export_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.csv'; -- Format: YYYYMMDD

-- Construct the bcp command
SET @bcpCommand = 'bcp "SELECT Column1, Column2, Column3 FROM YourDatabase.dbo.YourTable" queryout "' 
    + @exportPath + @fileName + '" '
    + '-S ' + @@SERVERNAME + ' -d YourDatabase -T -c -t, -r\n';

-- Execute the command
EXEC xp_cmdshell @bcpCommand;

• -T: Uses Windows authentication (replace with -U username -P password for SQL auth if needed)
• -c: Exports data in character format (avoids binary formatting issues)
• -t,: Sets comma as the field delimiter
• -r\n: Uses newline as the row terminator

2. Use OPENROWSET with the ACE OLE DB Provider #

If you prefer a pure T-SQL approach without invoking command-line tools, you can use OPENROWSET with the Microsoft ACE OLE DB Provider to write directly to a CSV file.

Prerequisite #

Install the Microsoft Access Database Engine 2016 Redistributable (or matching version) on your SQL Server to get the ACE provider. For 64-bit SQL Server, ensure you install the 64-bit driver.

Export Query #

DECLARE @exportPath NVARCHAR(255) = 'C:\Your\Target\Directory\';
DECLARE @fileName NVARCHAR(255) = 'daily_export_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.csv';

INSERT INTO OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;Database=' + @exportPath + ';HDR=YES;FMT=Delimited',
    'SELECT * FROM [' + @fileName + ']'
)
SELECT Column1, Column2, Column3 FROM YourDatabase.dbo.YourTable;

• HDR=YES: Includes column headers in the CSV
• FMT=Delimited: Specifies comma-separated values

Automate Daily Execution #

To run this query at a fixed time every day:

• Open SQL Server Management Studio (SSMS)
• Navigate to SQL Server Agent > Jobs
• Create a new job, add a T-SQL Script step with your export query
• Set up a Schedule for the job (choose daily, select your desired time)
• Save the job—it will run automatically on the schedule you set

Important Notes #

• Ensure the account running the SQL Server Agent service (or the account executing xp_cmdshell) has write permissions to the target directory.
• For security, consider disabling xp_cmdshell when not in use if your policies require it.

内容的提问来源于stack exchange，提问作者Carl Hussain