将数据导出到csv文件的存储过程只导出到一个文件 [英] Stored procedure that exports data into csv files only exports to one file
问题描述
我有一个有趣的脚本:
DECLARE @StartDT DATE
DECLARE @MinDOS DATE
DECLARE @TableName VARCHAR(50)
SET @TableName = 'ViewAccountDetail'
SELECT @MinDOS = MIN(dos) FROM accn_demographics
SELECT @StartDT =
CAST(CAST(datepart(YYYY,@MinDOS) AS varchar) + '-' + CAST(datepart(mm,@MinDOS) AS varchar) + '-' + CAST('01' AS varchar) AS DATETIME)
DECLARE @FileLocation VARCHAR(50)
WHILE @StartDT < '20110901'
BEGIN
SET @FileLocation='C:\test\'+@TableName+cast(@StartDT as varchar)+'.csv'
EXEC BCP_Text_File @TableName, @FileLocation
SET @StartDT = DATEADD(MONTH,1,@StartDT)
END
它应该做的是将数据导出到csv文件中.文件名应该是:
It is supposed to do is export data into csv files. The names of the files should be:
C:\test\ViewAccountDetail2011-01-01.csv
C:\test\ViewAccountDetail2011-02-01.csv
C:\test\ViewAccountDetail2011-03-01.csv
C:\test\ViewAccountDetail2011-04-01.csv
C:\test\ViewAccountDetail2011-05-01.csv
C:\test\ViewAccountDetail2011-06-01.csv
C:\test\ViewAccountDetail2011-07-01.csv
C:\test\ViewAccountDetail2011-08-01.csv
但它将所有数据存储到同一个:
but it stores all the data into the same one:
C:\test\ViewAccountDetail2011-01-01.csv
我做了一个 print @FileLocation
并确认它正确更新了这个变量.
i did a print @FileLocation
and confirmed that it correctly updates this variable.
我在这里遗漏了任何明显的、明显的东西吗?
is there any apparent, OBVIOUS thing that i am missing here?
仅供参考:
EXEC BCP_Text_File @TableName, @FileLocation
调用这个过程:
ALTER PROCEDURE [dbo].[BCP_Text_File]
@table NVARCHAR(255),
@filename VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(@table) IS NOT NULL
BEGIN
DECLARE
@sql NVARCHAR(MAX),
@cols NVARCHAR(MAX) = N'';
SELECT @cols += ',' + name
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@table)
ORDER BY column_id;
SELECT @cols = STUFF(@cols, 1, 1, '');
SET @sql = N'EXEC master..xp_cmdshell ''bcp "SELECT '''''
+ REPLACE(@cols, ',', ''''',''''') + ''''' UNION ALL SELECT '
+ 'RTRIM(' + REPLACE(@cols, ',', '),RTRIM(') + ') FROM '
+ DB_NAME() + '..' + @table + '" queryout "' + @filename + '" -c -T''';
EXEC sp_executesql @sql;
END
ELSE
BEGIN
SELECT 'The table '+@table+' does not exist in the database';
END
END
GO
非常感谢您的帮助和指导!!
thank you so much for your help and guidance!!
推荐答案
对我来说似乎很好用.我有几个建议:
Seems to work fine for me. I have a few suggestions:
(1) 停止进行所有字符串连接来构建日期.您可以更轻松地做同样的事情:
(1) stop doing all that string concatenation to build a date. You can do the same thing much easier as in:
SELECT @StartDT = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @MinDOS), '19000101');
(2) 停止声明varchar
没有长度.为了确保正确的输出,我更喜欢转换:
(2) stop declaring varchar
without length. And to ensure the right output, I prefer convert:
SET @FileLocation = 'C:\test\' + @TableName
+ CONVERT(CHAR(10), @StartDT, 120) + '.csv';
(3) 而不是调试"通过运行存储过程并检查文件夹中的输出来编写代码,为什么不先对输入进行完整性检查?另外,为什么要为日期使用两个变量?
(3) instead of "debugging" the code by running the stored procedure and inspecting the output in the folder, why not sanity-check your input first? Also, why use two variables for the date?
DECLARE
@StartDT DATE,
@TableName NVARCHAR(50),
@FileLocation VARCHAR(255);
SET @TableName = N'ViewAccountDetail';
SELECT @StartDT = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', MIN(dos)), '19000101')
FROM dbo.accn_demographics;
PRINT @StartDT;
-- ^^^^^ debugging 101 - what month do we think we're starting at?
WHILE @StartDT < '20110901'
BEGIN
SET @FileLocation = 'C:\test\' + @TableName
+ CONVERT(CHAR(10), @StartDT, 120) + '.csv';
PRINT @FileLocation;
--^^^^^ again, debugging 101 - what does the filename currently look like?
--EXEC BCP_Text_File @TableName, @FileLocation
SET @StartDT = DATEADD(MONTH, 1, @StartDT);
END
这篇关于将数据导出到csv文件的存储过程只导出到一个文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!