如何从 SQL Server 中的存储过程生成 csv 输出文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16079666/
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
How to produce an csv output file from stored procedure in SQL Server
提问by Al Phaba
Is it possible to generate a csv file from a stored procedure in SQL Server? I created my stored procedure and I want to stored some result as csv, does someone know how to achieve this?
是否可以从 SQL Server 中的存储过程生成 csv 文件？我创建了我的存储过程，我想将一些结果存储为 csv，有人知道如何实现吗？
回答by WowBow
回答by jitin14
You can do this using OPENROWSET as suggested in this answer. Reposting Slogmeister Extrarodinareanswer:
您可以按照本答案中的建议使用 OPENROWSET 执行此操作。转贴Slogmeister Extrarodinare答案：
Use T-SQL
使用 T-SQL
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 are 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.
您需要有可用的 Microsoft.ACE.OLEDB.12.0 提供程序。Jet 4.0 提供程序也可以使用，但它很古老，所以我改用了这个。
.CSV 文件必须已经存在。如果您使用标题 (HDR=YES)，请确保 .CSV 文件的第一行是所有字段的分隔列表。
回答by Devart
This script exports rows from specified tables to the CSV format in the output window for any tables structure. Hope, the script will be helpful for you -
此脚本在任何表结构的输出窗口中将指定表中的行导出为 CSV 格式。希望脚本对你有帮助 -
DECLARE
@TableName SYSNAME
, @ObjectID INT
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
'[' + s.name + '].[' + t.name + ']'
, t.[object_id]
FROM (
SELECT DISTINCT
t.[schema_id]
, t.[object_id]
, t.name
FROM sys.objects t WITH (NOWAIT)
JOIN sys.partitions p WITH (NOWAIT) ON p.[object_id] = t.[object_id]
WHERE p.[rows] > 0
AND t.[type] = 'U'
) t
JOIN sys.schemas s WITH (NOWAIT) ON t.[schema_id] = s.[schema_id]
WHERE t.name IN ('<your table name>')
OPEN [tables]
FETCH NEXT FROM [tables] INTO
@TableName
, @ObjectID
DECLARE
@SQLInsert NVARCHAR(MAX)
, @SQLColumns NVARCHAR(MAX)
, @SQLTinyColumns NVARCHAR(MAX)
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@SQLInsert = ''
, @SQLColumns = ''
, @SQLTinyColumns = ''
;WITH cols AS
(
SELECT
c.name
, datetype = t.name
, c.column_id
FROM sys.columns c WITH (NOWAIT)
JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
AND t.name NOT IN ('xml', 'geography', 'geometry', 'hierarchyid')
)
SELECT
@SQLTinyColumns = STUFF((
SELECT ', [' + c.name + ']'
FROM cols c
ORDER BY c.column_id
FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
, @SQLColumns = STUFF((SELECT CHAR(13) +
CASE
WHEN c.datetype = 'uniqueidentifier'
THEN ' + '';'' + ISNULL('''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''', ''NULL'')'
WHEN c.datetype IN ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary')
THEN ' + '';'' + ISNULL('''' + CAST(REPLACE([' + c.name + '], '''', '''''''') AS NVARCHAR(MAX)) + '''', ''NULL'')'
WHEN c.datetype = 'datetime'
THEN ' + '';'' + ISNULL('''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''', ''NULL'')'
ELSE
' + '';'' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), ''NULL'')'
END
FROM cols c
ORDER BY c.column_id
FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 10, 'CHAR(13) + '''' +')
DECLARE @SQL NVARCHAR(MAX) = '
SET NOCOUNT ON;
DECLARE
@SQL NVARCHAR(MAX) = ''''
, @x INT = 1
, @count INT = (SELECT COUNT(1) FROM ' + @TableName + ')
IF EXISTS(
SELECT 1
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(''tempdb..#import'')
)
DROP TABLE #import;
SELECT ' + @SQLTinyColumns + ', ''RowNumber'' = ROW_NUMBER() OVER (ORDER BY ' + @SQLTinyColumns + ')
INTO #import
FROM ' + @TableName + '
WHILE @x < @count BEGIN
SELECT @SQL = STUFF((
SELECT ' + @SQLColumns + ' + ''''' + '
FROM #import
WHERE RowNumber BETWEEN @x AND @x + 9
FOR XML PATH, TYPE, ROOT).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')
PRINT(@SQL)
SELECT @x = @x + 10
END'
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM [tables] INTO
@TableName
, @ObjectID
END
CLOSE [tables]
DEALLOCATE [tables]
In the output window you'll get something like this (AdventureWorks.Person.Person):
在输出窗口中，您将得到如下内容 (AdventureWorks.Person.Person)：
1;EM;0;NULL;Ken;J;Sánchez;NULL;0;92C4279F-1207-48A3-8448-4636514EB7E2;2003-02-08 00:00:00
2;EM;0;NULL;Terri;Lee;Duffy;NULL;1;D8763459-8AA8-47CC-AFF7-C9079AF79033;2002-02-24 00:00:00
3;EM;0;NULL;Roberto;NULL;Tamburello;NULL;0;E1A2555E-0828-434B-A33B-6F38136A37DE;2001-12-05 00:00:00
4;EM;0;NULL;Rob;NULL;Walters;NULL;0;F2D7CE06-38B3-4357-805B-F4B6B71C01FF;2001-12-29 00:00:00
5;EM;0;Ms.;Gail;A;Erickson;NULL;0;F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF;2002-01-30 00:00:00
6;EM;0;Mr.;Jossef;H;Goldberg;NULL;0;0DEA28FD-EFFE-482A-AFD3-B7E8F199D56F;2002-02-17 00:00:00
回答by Al Phaba
Found a really helpful link for that. Using SQLCMD for this is really easier than solving this with a stored procedure
为此找到了一个非常有用的链接。为此使用 SQLCMD 确实比使用存储过程解决这个问题更容易
http://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm
http://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm
回答by Dwayne Thompson
I also used bcp and found a couple other helpful posts that would benefit others if finding this thread
我还使用了 bcp 并找到了一些其他有用的帖子，如果找到这个帖子，这些帖子会让其他人受益
Don't use VARCHAR(MAX)as your @sqlor @cmdvariable for xp_cmdshell; you will get and error
不要用作 xp_cmdshell 的VARCHAR(MAX)您的@sql或@cmd变量；你会得到和错误
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1 Procedure expects parameter 'command_string' of type 'varchar'.
消息 214，级别 16，状态 201，过程 xp_cmdshell，第 1 行过程需要“varchar”类型的参数“command_string”。
http://www.sqlservercentral.com/Forums/Topic1071530-338-1.aspx
http://www.sqlservercentral.com/Forums/Topic1071530-338-1.aspx
Use NULLIFto get blanks for the csv file instead of a NUL(viewable in hex editor, or notepad++). I used that in the SELECTstatement for bcp
用NULLIF得到的csv文件，而不是一个空格NUL（十六进制编辑器，或记事本++可视范围）。我在SELECTbcp的语句中使用了它
How to make Microsoft BCP export empty string instead of a NUL char?
回答by MichaelEvanchik
Just to answer a native way to do this that finally worked, everything had to be casted as a varchar
只是为了回答一种最终有效的本地方式来做到这一点，一切都必须被转换为 varchar
ALTER PROCEDURE [clickpay].[sp_GetDocuments]
@Submid bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @raw_sql varchar(max)
DECLARE @columnHeader VARCHAR(max)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documents'
DECLARE @ColumnList VARCHAR(max)
SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +' AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documents'
SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM ' + 'Documents where Submid = ' + CAST(@Submid AS VARCHAR)
-- Insert statements for procedure here
exec(@raw_sql)
END
回答by Maddy
I have tried this and it is working fine for me:
我已经尝试过这个，它对我来说很好用：
sqlcmd -S servername -E -s~ -W -k1 -Q "sql query here" > "\file_path\file_name.csv"
