时间戳

首页 > 解决方案 > 在 MERGE 语句中替代 EXCEPT？

sql - 在 MERGE 语句中替代 EXCEPT？

问题描述

我正在编写一个查询以将我的 STG 表中的数据合并到我的数据表中，并且我想从 STG 表中排除在数据表中完全存在的任何数据。我编写了以下代码，它可以满足我的需要，但是 EXCEPT 语句会导致对数据表的大量读取。我可以尝试提高性能吗？

    MERGE INTO Table AS Target
USING
( /*EXCEPT STATEMENT REMOVES ANY ROWS WHICH HAVE BEEN MODIFIED, BUT NONE OF THE COLUMNS WHICH CHANGED WERE BROUGHT OVER*/
   SELECT    TimesheetEntryID
            ,UserID
            ,StartDateTime
            ,EndDateTime
            ,TimesheetTaskTypeID
            ,TaskID



    FROM Table_STG
EXCEPT
    SELECT   TimesheetEntryID
            ,UserID
            ,StartDateTime
            ,EndDateTime
            ,TimesheetTaskTypeID
            ,TaskID


    FROM Table

) AS Source

ON Target.TimesheetEntryID = Source.TimesheetEntryID

    WHEN MATCHED



    THEN

        UPDATE SET  Target.TimesheetEntryID = Source.TimesheetEntryID,
                    Target.UserID = Source.UserID,
                    Target.StartDateTime = Source.StartDateTime,
                    Target.EndDateTime = Source.EndDateTime,
                    Target.TimesheetTaskTypeID = Source.TimesheetTaskTypeID,
                    Target.TaskID = Source.TaskID



    WHEN NOT MATCHED BY TARGET THEN

    INSERT (
                 TimesheetEntryID
                ,UserID
                ,StartDateTime
                ,EndDateTime
                ,TimesheetTaskTypeID
                ,TaskID


            )

    VALUES  (   
                 Source.TimesheetEntryID
                ,Source.UserID
                ,Source.StartDateTime
                ,Source.EndDateTime
                ,Source.TimesheetTaskTypeID
                ,Source.TaskID

            );

标签： sqlsql-server

解决方案

您可以使用not in或not exists

不在

SELECT    TimesheetEntryID
        ,UserID
        ,StartDateTime
        ,EndDateTime
        ,TimesheetTaskTypeID
        ,TaskID

FROM Table_STG where (TimesheetEntryID ,UserID ,StartDateTime ,EndDateTime 
,TimesheetTaskTypeID ,TaskID) not in
(SELECT   TimesheetEntryID ,UserID ,StartDateTime ,EndDateTime ,TimesheetTaskTypeID 
,TaskID from table);

不存在

SELECT    TimesheetEntryID
        ,UserID
        ,StartDateTime
        ,EndDateTime
        ,TimesheetTaskTypeID
        ,TaskID

FROM Table_STG STG where (TimesheetEntryID ,UserID ,StartDateTime ,EndDateTime 
,TimesheetTaskTypeID ,TaskID) not exitst
(SELECT 1 from table where TimesheetEntryID = STG.TimesheetEntryID  ,UserID = 
STG.UserID ,StartDateTime = STG.StartDateTime
,EndDateTime = STG.EndDateTime ,TimesheetTaskTypeID = STG.TimesheetTaskTypeID ,TaskID 
= STG.TaskID)

推荐阅读