SQL Server：使用合并操作更新表行

Question

你好，我正在尝试使用动态变量更新表格，但是我一直得到“语法不正确”的错误提示，你能帮我看看我哪里出错了吗？

这是当我运行查询时收到的信息。

select convert(varchar(55),refdate)+'-'+convert(varchar(55),refcount) FROM [gen_048_MAR2016]

      MERGE gen_048_MAR2016 as target             USING #temp1 as source
          ON target.refcount = source.refnum
              AND sourc3e.tsql = target.refcount
        WHEN MATCHED THEN
          UPDATE
              SET
              target.stat = source.stat
              target.statdate = source.statdate           WHEN NOT MATCHED BY TARGET THEN
        INSERT (stat, statdate)
        VALUES (S.stat, S.statdate)                   
            ; Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'target'.

ERROR: Incorrect syntax near 'target'.

我没有写完整的查询语句，所以我只复制和粘贴了我认为出错的部分。

CREATE TABLE #records(
         [index] int PRIMARY KEY IDENTITY
        ,refnum varchar(200)
        ,stat varchar(200)
        ,statdate varchar(200)
    )

insert into #records (refnum, stat, statdate) 
select 
dbo.fn_Parsename(WHOLEROW,'|',0),
dbo.fn_Parsename(WHOLEROW,'|',3),
dbo.fn_Parsename(WHOLEROW,'|',4)

from #temp1

declare @refnum varchar(100)
declare @stat varchar(100)
declare @statdate varchar(100)
declare @sql NVARCHAR(MAX),
declare @index int


WHILE (@index <= (SELECT MAX([index]) FROM #records))


    BEGIN

    set @stat = (select stat from #records where [index] = @index)
    select @stat
    set @statdate = (select statdate from #records where [index] = @index)
    select @statdate

    set @refnum = (select refnum from #records where [index] = @index)
    set @refnum = replace(@refnum, 'F', '')
    select @refnum

    set @sql = '
        MERGE '+@sourceTable+' T
        USING #temp1 S
            ON T.refcount = S.refnum
                AND S.tsql = T.refcount
        WHEN MATCHED THEN
            UPDATE
                SET 
                T.stat = S.stat
                T.statdate = S.statdate
        WHEN NOT MATCHED BY TARGET THEN
          INSERT (stat, statdate)
          VALUES (S.stat, S.statdate)                   
            ;'
            select @refnum, @stat, @statdate
            print @sql
            exec (@sql)

        SELECT 'File has been successfully uploaded', @fileDate,'success' as msg

      set @index = @index + 1

   END

我没有把@sourceTable字符串放在代码中以减少代码量，但如果需要该信息，我随时可以添加它。

- Vince Osana

---

MERGE语句必须以分号（;）结尾。您做到了吗？ - Deep Kalra

在变量替换后，编辑您的问题并打印出merge语句。 - Gordon Linoff

是的，你说得对~！@DeepanshuKalra - Vince Osana

@VinceOsana Gordon的意思是，你需要分享Merge语句，这样他才能告诉你问题出在哪里。而且你可以通过使用PRINT而不是EXEC来获取SQL。 - Deep Kalra

http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 - user330315

Answer 1

1. 在您实际的动态SQL中，变量替换后，您使用target和source保留字作为表别名。请使用方括号[target]，或者更好的方法是像“整个查询”代码示例中一样只使用T。
2. 在T.stat = S.stat之后的动态SQL中，您漏掉了逗号。

- Y.B.