Sql server 2008R2合并语句并不总能找到匹配项（即使它存在） [英] Sql server 2008R2 merge statement doesn't always find a match (even though it exists)

问题描述

我正在使用SQL Server合并语句从另一个表更新一个表（如果目标中的记录与源中的记录匹配，则没有任何反应，否则执行插入。



理论上，如果我连续两次运行相同的查询，则不应该在查询的第二次运行时执行任何插入，但是我得到了一些仍在执行的插入我不知道为什么。



当我将已经存在的记录与我想要插入的记录进行比较时，连接的列值是相同的（这是应该表示匹配，因此没有插入），但Merge语句仍然插入源记录。



每次执行Merge语句时，同样的少数记录被重新插入。



我尝试过：



我一直在玩这个代码。我已经更新了下面的代码来显示它的当前状态。请查看评论奇怪的代码块：

I'm using the SQL Server merge statement to update one table from another (if a record in the target matches a record in the source, nothing happens, otherwise an insert is performed.

Theoretically, if I run the same query twice in a row, there shouldn't be any inserts performed on the 2nd run of the query, but I'm getting a handful of inserts that are still being performed. I have no idea why.

When I compare the record that already exists with the record I'm trying to insert, the joined column values are identical (which is supposed to indicate a match, and thus no insert), yet the Merge statement still inserts the source record.

Every time I execute the Merge statement, the same handful of records are re-inserted.

What I have tried:

I've been playing with this code. I've updated the code below to show its current state. Please review the comments in the code block for the weirdness:

-- I'm using this variable to indicate when a record was inserted
DECLARE @nextOrdinal INT = (SELECT CASE WHEN MAX(InsertOrdinal) IS NULL THEN 1 
                                        ELSE MAX(InsertOrdinal)+1 
                                        END 
                            FROM [Essentris].[dbo].[VancoMycin]);
    
-- create a temporary table to hold our grouped/normalized data
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
    DROP TABLE #tempVanco;
END
        
-- this temp table holds our grouped and normalized data
CREATE TABLE #tempVanco
(
    [ABX_NAME]      [nvarchar](255) NULL,
    [ROUTE]         [nvarchar](255) NULL,
    [DELIVERY_TIME] [datetime]      NULL,
    [HOSPNO]        [int]             NULL,
    [PTNAME]        [nvarchar](255) NULL,
    [UNIT]          [nvarchar](255) NULL,
    [ATTENDING]     [nvarchar](255) NULL,
    [SERVICE]       [nvarchar](255) NULL,
    [ADX]           [nvarchar](255) NULL
);


-- Normalize the data so that there are is no unexpected stuff in any of the fields. This 
-- also keeps me from having to do this further down in the code in the cte (this is a 
-- desperation measure after fighting with this for DAYS)

update  [Essentris].[dbo].[IMPORTED_Vanco]
SET     [ABX_NAME] = UPPER(RTRIM(LTRIM([ABX_NAME])))
        ,[ROUTE]    = UPPER(RTRIM(LTRIM([ROUTE])))
        ,[PTNAME]   = UPPER(RTRIM(LTRIM([PTNAME])))
        ,[UNIT]     = UPPER(RTRIM(LTRIM([UNIT])))
        ,[ATTENDING]= UPPER(RTRIM(LTRIM([ATTENDING])))
        ,[SERVICE]  = UPPER(RTRIM(LTRIM([SERVICE])))
        ,[ADX]      = UPPER(RTRIM(LTRIM([ADX])));
    
-- group the imported table data (the data may have duplicate rows)

;with cte as 
(
    SELECT  [ABX_NAME]
            ,[ROUTE]
            ,[DELIVERY_TIME]
            ,CAST([HOSPNO] AS INT) AS [HOSPNO]
            ,[PTNAME]
            ,[UNIT]
            ,[ATTENDING]
            ,[SERVICE]
            ,[ADX]
    FROM    [Essentris].[dbo].[IMPORTED_Vanco]
    GROUP BY [ABX_NAME]
            ,[ROUTE]
            ,[DELIVERY_TIME]
            ,CAST([HOSPNO] AS INT)
            ,[PTNAME]
            ,[UNIT]
            ,[ATTENDING]
            ,[SERVICE]
            ,[ADX]
)
-- and insert it into the temp table from the cte
insert into #tempvanco 
select * from cte;

-- Up to this point, the contents of #tempvanco is as expected

--================

-- merge #tempvanco into our concrete table. 

MERGE INTO [Essentris].[dbo].[VancoMycin] AS t
USING #tempVanco AS s
ON	
(
    -- this is really weird: if I just use the three fields below, it reinserts fewer 
    -- records than if I use ALL of the fields. This hints at there being a problem 
    -- with one or more of the string fields, but after they've been normalized this 
    -- theoretically should not be the case.
        t.[DELIVERY_TIME] = s.[DELIVERY_TIME]
    AND t.[HOSPNO]        = s.[HOSPNO]
    -- I tried using "like" instead of "=", and it had no effect
    AND	t.[PTNAME]        like s.[PTNAME]
    --and t.[ABX_NAME]      = s.[ABX_NAME]
    --and t.[ROUTE]         = s.[ROUTE]
    --and t.[UNIT]          = s.[UNIT]
    --and t.[ATTENDING]     = s.[ATTENDING]
    --and t.[SERVICE]       = s.[SERVICE]
    --and t.[ADX]           = s.[ADX]
)

-- We should never need to update anything, so we only react when a record is new

WHEN NOT MATCHED BY TARGET THEN
    INSERT 
    (
        [ABX_NAME]
        ,[ROUTE]
        ,[DELIVERY_TIME]
        ,[HOSPNO]
        ,[PTNAME]
        ,[UNIT]
        ,[ATTENDING]
        ,[SERVICE]
        ,[ADX]

        -- we need to create some data to fill in these fields
        ,[ABX_NAME_SHORT]
        ,[DELIVERY_DATE]
        ,InsertOrdinal
    )
    VALUES
    (
        s.[ABX_NAME]
        ,s.[ROUTE]
        ,s.[DELIVERY_TIME]
        ,s.[HOSPNO]
        ,s.[PTNAME]
        ,s.[UNIT]
        ,s.[ATTENDING]
        ,s.[SERVICE]
        ,s.[ADX]

        -- created data
        ,'VANCOMYCIN'
        ,CONVERT(DATE, s.[DELIVERY_TIME])
        ,@nextOrdinal
    );

-- drop the temporary table

IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL 
BEGIN
    DROP TABLE #tempVanco;
END

推荐答案

我终于让它工作了创建一个视图，对记录进行分组，并在合并语句中使用该视图。



我仍​​然不知道是什么导致sql server在临时表上呕吐...



------------------------



更新：它似乎是SQL Server 2008R2中的一个错误。我在SQL Express 2012上在家里尝试过，问题似乎不会影响该版本。

I finally got it to work by creating a view that grouped the records, and using that view in the merge statement.

I still don't know what was causing sql server to puke on the temp table...

------------------------

Update: It appears to be a bug in SQL Server 2008R2. I tried it at home on SQL Express 2012, and the problem doesn't appear to affect that version.

这篇关于Sql server 2008R2合并语句并不总能找到匹配项（即使它存在）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！