在 SQL Server 中合并 - 语法不正确

Question

我尝试执行此事务：

GO
BEGIN TRAN;
MERGE A AS t 
USING B AS tmp
ON (t.domain = tmp.domain and t.link=tmp.link) 
WHEN NOT MATCHED BY A
    THEN INSERT(ipVal, domain, dateStart, dateUpdate, dateFinish, link) VALUES(tmp.ipVal, tmp.domain, tmp.dateStart, tmp.dateUpdate, tmp.dateFinish, tmp.link)
WHEN MATCHED 
    THEN UPDATE SET t.dateupdate = tmp.dateupdate
WHEN NOT MATCHED BY B
    THEN UPDATE SET t.datefinish="a"
ROLLBACK TRAN;
GO 

我

从这里获取了这段代码，但是当我尝试执行它时，我遇到了一个错误：

Incorrect syntax near 'A'

可能是什么问题？

Answer 1

根据MSDN，WHEN NOT MATCHED分支的简化语法是

   [ WHEN NOT MATCHED [ BY TARGET ] [...]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [...]
        THEN <merge_matched> ] [ ...n ]

解决方案是用WHEN NOT MATCHED BY TARGET替换WHEN NOT MATCHED BY A，用WHEN NOT MATCHED BY SOURCE替换WHEN NOT MATCHED BY B。

TARGET和SOURCE是 T-SQL 关键字，而不是占位符。

Answer 2

我刚开始使用MERGE的一个，我使用这种语法来正确理解它：

MERGE A AS target
USING B AS source
ON (target.domain = source.domain and target.link=source.link) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(ipVal, domain, dateStart, dateUpdate, dateFinish, link)
    VALUES(source.ipVal, source.domain, source.dateStart, source.dateUpdate, source.dateFinish, source.link)
WHEN MATCHED 
    THEN UPDATE SET target.dateupdate = source.dateupdate
WHEN NOT MATCHED BY SOURCE
    THEN UPDATE SET target.datefinish="a"

我知道这是一种不好的做法，但开始时 - 有很大帮助。

由于@BogdanSahlean点TARGET和SOURCE是关键字，因此在确定匹配/不匹配的语句时需要关键字。

Answer 3

看起来您正在尝试使用不可转换为日期或日期时间类型的字符或 varchar 类型数据更新日期或日期时间类型列。"a"不可转换为日期或日期时间。看看我在下面的第一个和第二个合并。如果未提及何时匹配或何时与源或目标不匹配，则默认情况下会考虑"目标"。

--DROP TABLE #A;
--DROP TABLE #B;
CREATE TABLE #A
    (
        ipval VARCHAR(50)
    , domain CHAR(5)
    , dateStart DATE
    , dateUpdate DATE
    , dateFinish DATE
    , link VARCHAR(50)
    );
CREATE TABLE #B
    (
        ipval VARCHAR(50)
    , domain CHAR(5)
    , dateStart DATE
    , dateUpdate DATE
    , dateFinish DATE
    , link VARCHAR(50)
    );
INSERT  INTO #B
        ( ipval, domain, dateStart, dateUpdate, dateFinish, link )
VALUES  ( '42.130.239.56'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 1, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 5, GETDATE())  -- dateFinish - date
            , 'www.stackoverflow'  -- link - varchar(50)
            ),
        ( '78.188.136.74'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 2, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 10, GETDATE())  -- dateFinish - date
            , 'www.msdn'  -- link - varchar(50)
            );
INSERT  INTO #A
        ( ipval, domain, dateStart, dateUpdate, dateFinish, link )
VALUES  ( '30.48.111.20'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 5, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 10, GETDATE())  -- dateFinish - date
            , 'www.msdn'  -- link - varchar(50)
            ),
        ( '30.48.111.20'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 5, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 10, GETDATE())  -- dateFinish - date
            , 'www.gmail'  -- link - varchar(50)
            );
--First Merge
BEGIN TRAN;
MERGE #A AS T
USING #B AS tmp
ON T.domain = tmp.domain
    AND T.link = tmp.link
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( ipval
            , domain
            , dateStart
            , dateUpdate
            , dateFinish
            , link
            )
    VALUES ( tmp.ipval
            , tmp.domain
            , tmp.dateStart
            , tmp.dateUpdate
            , tmp.dateFinish
            , tmp.link
            )
WHEN MATCHED THEN
    UPDATE SET T.dateUpdate = tmp.dateUpdate
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.dateFinish =''a'';
COMMIT TRAN;
GO

SELECT  *
FROM    #A;
SELECT  *
FROM    #B;
--Second Merge
BEGIN TRAN;
MERGE #A AS T
USING #B AS tmp
ON T.domain = tmp.domain
    AND T.link = tmp.link
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( ipval
           , domain
           , dateStart
           , dateUpdate
           , dateFinish
           , link
           )
    VALUES ( tmp.ipval
           , tmp.domain
           , tmp.dateStart
           , tmp.dateUpdate
           , tmp.dateFinish
           , tmp.link
           )
WHEN MATCHED THEN
    UPDATE SET T.dateUpdate = tmp.dateUpdate
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.dateFinish = CAST(GETDATE() AS DATE);
COMMIT TRAN;
GO

SELECT  *
FROM    #A;
SELECT  *
FROM    #B;