Sql server 2008R2 merge statement doesn't always find a match (even though it exists)
-
Yes, I posted the same question in QA and it's been there for a day with no responses, so I posted it here too... 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 on the same source table, 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. I've been playing with this code. 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 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(RTRI -
Yes, I posted the same question in QA and it's been there for a day with no responses, so I posted it here too... 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 on the same source table, 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. I've been playing with this code. 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 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(RTRII found a work around. Instead of directly using the Imported_XXX table directly, or using a temporary table, I creates a view that performed the grouping, and that seems to have fixed my problem. It has been suggested that there might be a bug in the Merge functionality.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
