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:
DECLARE @nextOrdinal INT = (SELECT CASE WHEN MAX(InsertOrdinal) IS NULL THEN 1
ELSE MAX(InsertOrdinal)+1
END
FROM [Essentris].[dbo].[VancoMycin]);
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
DROP TABLE #tempVanco;
END
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
);
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])));
;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]
)
insert into #tempvanco
select * from cte;
MERGE INTO [Essentris].[dbo].[VancoMycin] AS t
USING #tempVanco AS s
ON
(
t.[DELIVERY_TIME] = s.[DELIVERY_TIME]
AND t.[HOSPNO] = s.[HOSPNO]
AND t.[PTNAME] like s.[PTNAME]
)
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[ABX_NAME]
,[ROUTE]
,[DELIVERY_TIME]
,[HOSPNO]
,[PTNAME]
,[UNIT]
,[ATTENDING]
,[SERVICE]
,[ADX]
,[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]
,'VANCOMYCIN'
,CONVERT(DATE, s.[DELIVERY_TIME])
,@nextOrdinal
);
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
DROP TABLE #tempVanco;
END