关键字“as”附近的 BCP 错误

我是使用 BCP 的新手，但我正在尝试提取通过存储过程生成的数据。这是我最初的问题的延续，这是一个带有临时表的存储过程。这个过程有点长，但核心是一样的，生成临时表，运行动态 SQL 来填充表，然后返回数据。

我很快意识到 bcp 通过错误不喜欢临时表：

SQLState = S0002，NativeError = 208

错误 = [Microsoft][SQL Server Native Client 10.0][SQL Server]无效的对象名称'#SurveyData

因此，我更改了我的程序以删除 #temp 表并使用实际表。但现在我得到一个错误：

SQLState = 37000，NativeError = 156

错误 = [Microsoft][SQL Server Native Client 10.0][SQL Server]关键字“as”附近的语法不正确

我相信问题出在动态 SQL 上。声明中有一个as。

SET @sql = 'INSERT INTO cl.Results ' +
                            ' SELECT s.SurveyId
                                , s.InstanceId
                                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END 
                                , Cast(s.' + @ColumnName + ' as varchar(1000))
                            FROM cl.SurveyData t 
                            INNER JOIN dbo.' + @TableName + ' s' +
                            ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                            ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

我在 BCP 中用来执行的代码是：

bcp "exec dbo.getresults" queryout "c:\temp\mytext.txt" -S <myserver> -T -c -t^|

我愿意接受有关如何提取这些数据的任何建议，即使我必须重写存储过程。

我不能使用 BCP 来执行带有动态 SQL 的存储过程吗？如果没有，我还应该如何考虑提取这些数据？SSIS 似乎也不喜欢临时表或动态 SQL。

编辑：使用print(@sql)

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_1' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_1 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_1 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  WHERE t.columnName = 'Q_1'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_2' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_2 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  WHERE t.columnName = 'Q_2'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_3' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_3 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  
WHERE t.columnName = 'Q_3'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_4' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_4 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  
WHERE t.columnName = 'Q_4'

编辑：这是我在没有任何临时表的情况下运行的完整脚本：

truncate table cl.Results
truncate table cl.SurveyData

-- insert the survey table structures for use
insert into cl.SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM cl.SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM cl.SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO cl.Results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END 
                        , Cast(s.' + @ColumnName + ' as varchar(1000))
                    FROM cl.SurveyData t 
                    INNER JOIN dbo.' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT r.SurveyId, 
        r.InstanceId, 
        CASE 
            when chat.DataName = 'BRKR_ACCT_ID' and chat.DataValue is not null then chat.DataValue
            when email.PropName = 'ACCT_ID' and email.PropValue is not null then email.PropValue
        END Account_ID,
        CASE 
            when chat.DataName = 'BRKR_CUST_ID' and chat.DataValue is not null then chat.DataValue
            when email.PropName = 'CUST_ID' and email.PropValue is not null then email.PropValue
        END Cust_ID,
        case 
            when chat.LoginName is null 
            then email.LoginName 
            else chat.LoginName 
        end SchwabId,
        case 
            when chat.CustEmail is null 
            then email.ReplyTo 
            else chat.CustEmail 
        end CustomerEmail,
        sp.DateSent,
        CONVERT(varchar, DATEADD(ms, DATEDIFF(SECOND, sp.datesent, sp.datecompleted) * 1000, 0), 108) ResponseTime,
        r.QuestionNumber,
        r.Response
    FROM cl.Results r
    INNER JOIN dbo.SurveyParam sp
        ON r.InstanceId = sp.InstanceID
        AND r.SurveyId = sp.SurveyID
    -- chat surveys uses Param4
    LEFT JOIN 
    (
        SELECT si.SessionID, 
            si.CustEmail, 
            u.LoginName, 
            ltrim(rtrim(sie.DataValue)) DataValue, 
            ltrim(rtrim(sie.DataName)) DataName
        FROM dbo.SessionInfo si
        LEFT JOIN dbo.Users u
            ON si.LastAgent = u.id
        LEFT JOIN dbo.SessionInfoExternals sie
            ON si.SessionID = sie.SessionID
        WHERE sie.DataName IN ('BRKR_ACCT_ID', 'BRKR_CUST_ID')
    ) chat
        ON sp.Param4 = chat.SessionID
    -- email surveys uses Param3
    LEFT JOIN
    (
        SELECT mm.MsgID, 
            mr.ReplyTo, 
            u.LoginName, 
            ltrim(rtrim(mie.PropValue)) PropValue,
            ltrim(rtrim(mie.PropName)) PropName
        FROM dbo.MailReply mr
        INNER JOIN dbo.MailMessage mm
            ON mr.ReplyToID = mm.MsgID
        LEFT JOIN dbo.Users u
            on mr.AgentID = u.ID
        LEFT JOIN dbo.MsgInfoExternals mie
            ON mm.IncidentID = mie.Instance
        WHERE mie.PropName IN ('CUST_ID', 'ACCT_ID')
    ) email
        ON sp.Param3 = email.MsgID
    WHERE sp.DateCompleted is not null