SQL Server：将查询导出为 .txt 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20620198/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server : export query as a .txt file
提问by SRahmani
I am trying to export my SQL Server query results into a folder in .txtformat (this is for an automated job)
我正在尝试将我的 SQL Server 查询结果以.txt格式导出到文件夹中（这是针对自动化作业的）
I know the equivalent in MySQL works with INTO OUTFILE. Does anyone know the best way to do this in SQL Server 2008 Management Studio?
我知道 MySQL 中的等价物适用于INTO OUTFILE. 有谁知道在 SQL Server 2008 Management Studio 中执行此操作的最佳方法？
SELECT DISTINCT RTRIM (s1.SGMNTID) AS 'AccCode',RTRIM (s1.DSCRIPTN) AS 'CodeDesc', CASE
WHEN s1.SGMTNUMB = '1' THEN '1'
WHEN s1.SGMTNUMB = '2' THEN '2'
WHEN s1.SGMTNUMB = '3' THEN '110'
WHEN s1.SGMTNUMB = '4' THEN '4'
WHEN s1.SGMTNUMB = '5' THEN '120'
END AS 'AccountType_id',
CASE WHEN s1.SGMTNUMB = '2'
THEN LEFT(s1.SGMNTID, 2)
ELSE 'DEFAULT'
END AS 'AccGroupName'
FROM GL40200 s1
UNION
SELECT REPLACE ([ACTNUMBR_1]+'-'+ [ACTNUMBR_2]+'-'+ [ACTNUMBR_3]+'-'+[ACTNUMBR_4]+'-'+ [ACTNUMBR_5],' ', '') AS 'AccCode',
'' AS 'CodeDesc',
'0' AS 'AccountType_id',
'Default' AS 'AccGroupName'
FROM GL00100 a
INTO OUTFILE 'C:\Users\srahmani\verian/myfilename.txt'
回答by D Stanley
you do this in the SSMS app, not the SQL. In the toolbar select
您在 SSMS 应用程序中执行此操作，而不是在 SQL 中执行此操作。在工具栏中选择
Query --> Results To --> Results To File
查询 --> 结果到 --> 结果到文件
回答by Frank N Stein
Another way is from command line, using the osql:
另一种方法是从命令行，使用 osql：
OSQL -S SERVERNAME -E -i thequeryfile.sql -o youroutputfile.txt
This can be used from a BAT file and shceduled by a windows user to authenticated.
这可以从 BAT 文件中使用，并由 Windows 用户进行身份验证。
回答by Mihai Bejenariu
You can use bcp utility.
您可以使用bcp 实用程序。
To copy the result set from a Transact-SQL statement to a data file, use the queryout option. The following example copies the result of a query into the Contacts.txt data file. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command. At the Windows command prompt, enter:
要将结果集从 Transact-SQL 语句复制到数据文件，请使用 queryout 选项。以下示例将查询结果复制到 Contacts.txt 数据文件中。该示例假定您使用的是 Windows 身份验证，并且与您在其上运行 bcp 命令的服务器实例具有可信连接。在 Windows 命令提示符下，输入：
bcp "<your query here>" queryout Contacts.txt -c -T
You can use BCP by directly calling as operating sytstem command in SQL Agent job.
您可以通过在 SQL Agent 作业中直接作为操作系统命令调用来使用 BCP。
回答by Dan Pickard
You can use windows Powershell to execute a query and output it to a text file
您可以使用 Windows Powershell 执行查询并将其输出到文本文件
Invoke-Sqlcmd -Query "Select * from database" -ServerInstance "Servername\SQL2008" -Database "DbName" > c:\Users\outputFileName.txt
Invoke-Sqlcmd -Query "Select * from database" -ServerInstance "Servername\SQL2008" -Database "DbName" > c:\Users\outputFileName.txt
回答by CodeBreaker
The BCP Utility can also be used in the form of a .bat file, but be cautious of escape sequences (ie quotes "" must be used in conjunction with ) and the appropriate tags.
BCP 实用程序也可以 .bat 文件的形式使用，但要注意转义序列（即引号 "" 必须与 结合使用）和适当的标签。
.bat Example:
.bat 示例：
C:
bcp "\"YOUR_SERVER\".dbo.Proc" queryout C:\FilePath.txt -T -c -q
-- Add PAUSE here if you'd like to see the completed batch
-q MUST be used in the presence of quotations within the query itself.
-q 必须在查询本身中出现引号时使用。
BCP can also run Stored Procedures if necessary. Again, be cautious: Temporary Tables must be created prior to execution or else you should consider using Table Variables.
如有必要，BCP 还可以运行存储过程。再次提醒：必须在执行之前创建临时表，否则您应该考虑使用表变量。
回答by Bishwas Mishra
This is quite simple to do and the answer is available in other queries. For those of you who are viewing this:
这很容易做到，答案可以在其他查询中找到。对于正在查看此内容的人：
select entries from my_entries where id='42' INTO OUTFILE 'bishwas.txt';
