如何自动将SQL查询结果保存到.csv文件 [英] How to automatically save sql query results to .csv file
问题描述
我正在尝试导出我的sql查询结果，当您执行查询时，它会自动完成.我已经看到了尝试使用 output 的示例，但这会给我带来语法错误，因为示例 Select * From dbo.anyTable Output to 我在查询中有一个位置和内部联接的地方，后面有一个 a ，这就是我认为的问题所在.我知道可以通过右键单击鼠标将其保存并将结果另存为，但是这并不是我理想中想要的，因为我想使我正在尝试执行的整个系统自动化.
I am trying to export my sql query results which I would like to do it automatically when you execute the query. I have seen examples of using output to which I tried to use but this would give me a syntax error as the examples did Select * From dbo.anyTable Output to but for me where I have a where and inner join in my query I have a ) a after it which is what I think is the problem. I know it is possible to save it by right clicking the mouse and save results as, but this is not what I want ideally as I would like to automate the whole system I am trying to do.
例如，我想尝试对部分查询执行此操作:
For example I would like to try and do this with part of my query:
Select top 1 a.NAME, COUNT(*) OVER() AS totalRows
From (Select de.NAME From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @filecontent = de.ENVIRONMENT_ID) a
OUTPUT TO @myPath
FORMAT TEXT
QUOTE '"'
WITH COLUMN NAMES;
编辑我有以下查询，但没有生成文件，或者有任何错误，我在做什么错了?
EDIT I have the following query but it doesn't produce a file or any error's what am I doing wrong?
Set @OutputFilePath = 'C:\DeploymentPipelines'
Set @ExportSQL = 'EXEC master.dbo.xp_cmdshell ''bcp
"Select top 1 a.NAME, COUNT(*) OVER() AS totalRows
From (Select de.NAME From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @filecontent = de.ENVIRONMENT_ID) a"
queryout "' + @OutputFilePath + '\results4.csv" -T -c -t -S DEV-BUILD01\SQLSERVER'''
Exec(@ExportSQL)
推荐答案
我有一些存储的proc用来修改.csv文件，我发现使用bcp是在TSQL脚本中执行此操作的最佳方法.语法是这样的(摘自我的一个在线示例；
I've got some stored procs that do this for modifying .csv files, I found that using bcp was my best method for doing this in a TSQL script. The syntax is like this (taken from one of my live examples;
DECLARE @OutputFilePath nvarchar(max); SET @OutputFilePath = 'C:\Users\VirtualMachine1\Desktop\MasterFullOutput\Phase03'
DECLARE @ExportSQL nvarchar(max); SET @ExportSQL = N'EXEC master.dbo.xp_cmdshell ''bcp "SELECT TextData FROM DataConversionDB.dbo.DataScripts ORDER BY RowNumber" queryout "' + @OutputFilePath + '\OutputData.csv" -T -c -t -S WIN-SIITTJOB7OV'''
EXEC(@ExportSQL)
您将必须确保SQL Server服务登录名可以访问您要输出到的文件路径(不是您自己的权限，即SQL Server NT Service的权限).
You will have to make sure that your SQL Server service login has access to the file path where you are outputting to (not your own permissions, the permissions of the SQL Server NT Service).
这是有关bcp命令的其他阅读内容；
Here's some additional reading around the bcp command;
https://msdn.microsoft.com/zh-CN/library/aa337544.aspx
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/
要缩小范围，请举一个例子，看看会发生什么；
To narrow this down, give this example a go and see what happens;
DECLARE @ExportSQL nvarchar(max);
SET @ExportSQL = 'EXEC ..xp_cmdshell ''bcp "SELECT TOP 1 FROM sys.objects " queryout "C:\DeploymentPipelines\results4.csv" -T -c -t -S DEV-BUILD01\SQLSERVER'''
Exec(@ExportSQL)
这篇关于如何自动将SQL查询结果保存到.csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
