TSQL-导出到多个文本文件

Question

因此，我需要将ID号从表导出到多个文本文件（300张），我正在尝试使用BCP来实现这一目标，并一直在尝试使用以下内容：

DECLARE  @First             int
        ,@Last              int
        ,@Range             INT
        ,@docName           NVARCHAR(20)
        ,@SQLCommand        NVARCHAR(MAX)
SELECT @First   = MIN(CandidateID)  FROM tblCandidate
SELECT @Last    = MAX(CandidateID)  FROM tblCandidate
SELECT @DocName = 1
SELECT @Range   = 300
WHILE @First <= @Last
BEGIN
    SELECT @docName = 1
    SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "SELECT CandidateID FROM db1.dbo.tblCandidate WHERE [CandidateID] = ''''' 
                    + 'BETEEN ' +  CAST(@First AS NVARCHAR) + ' AND ' + CAST(@Range AS NVARCHAR) 
                    + '''''" queryout "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackuptest' + CAST(@DocName AS NVARCHAR(20)) + '.txt" -T -c -t,''' 
    PRINT @SQLCommand
    EXEC (@SQLCommand)
    SET @First = @First + 300
    SET @Range = @Range + 300
    SET @DocName = @DocName + 1
END

但是，我一直遇到以下错误：

Starting copy...
SQLState = 22005, NativeError = 245
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the varchar value 'BETEEN 1 AND 300' to data type int.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed

有什么想法我在这里做错了什么？

----编辑：-------------------------------------------------------------------------------------------------------------------------

将SQLCommand更改为：

SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "SELECT CandidateID FROM TRIS_Aspen.dbo.tblCandidate WHERE [CandidateID] ''''' 
                + 'BETWEEN ' +  CAST(@First AS NVARCHAR) + ' AND ' + CAST(@Range AS NVARCHAR) 
                + '''''" queryout "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackuptest"' + CAST(@DocName AS NVARCHAR(20)) + '.txt" -T -c -t,''' 

现在正在收到以下错误：

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file
NULL

Answer 1

两个错字：

• [CandidateID] =-在此列上使用BETWEEN时，在此处丢失平等
• BETEEN-更改为BETWEEN

尝试以下操作：

SET @SQLCommand = 'EXEC xp_cmdshell ''bcp "SELECT CandidateID FROM TRIS_Aspen.dbo.tblCandidate WHERE [CandidateID] ''''' 
                    + 'BETWEEN ' +  CAST(@First AS NVARCHAR) + ' AND ' + CAST(@Range AS NVARCHAR) 
                    + '''''" queryout "C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackuptest' + CAST(@DocName AS NVARCHAR(20)) + '.txt" -T -c -t,'''

Answer 2

之间的命令之间只需要看起来像

[CandidateID] BETWEEN 1 AND 300

您的代码正在生产

[CandidateID] ''' BETWEEN 1 AND 300

松散不必要的引号