Click here to Skip to main content
15,506,023 members
Home / Discussions / Database
   

Database

 
AnswerRe: Name for Sql Table Pin
Eddy Vluggen8-Sep-17 2:02
professionalEddy Vluggen8-Sep-17 2:02 
AnswerRe: Name for Sql Table Pin
Richard MacCutchan8-Sep-17 6:21
mveRichard MacCutchan8-Sep-17 6:21 
AnswerRe: Name for Sql Table Pin
jschell8-Sep-17 9:21
jschell8-Sep-17 9:21 
QuestionVisual studio 2010 ssis Pin
Joanna Webster7-Sep-17 2:43
Joanna Webster7-Sep-17 2:43 
GeneralRe: Visual studio 2010 ssis Pin
Richard MacCutchan7-Sep-17 5:43
mveRichard MacCutchan7-Sep-17 5:43 
AnswerRe: Visual studio 2010 ssis Pin
Simon_Whale7-Sep-17 6:06
professionalSimon_Whale7-Sep-17 6:06 
AnswerRe: Visual studio 2010 ssis Pin
Chris Quinn7-Sep-17 6:48
Chris Quinn7-Sep-17 6:48 
QuestionSql server 2008R2 merge statement doesn't always find a match (even though it exists) Pin
#realJSOP31-Aug-17 5:13
mva#realJSOP31-Aug-17 5:13 
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:
SQL
-- 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(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
".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


modified 31-Aug-17 11:06am.

AnswerRe: Sql server 2008R2 merge statement doesn't always find a match (even though it exists) Pin
#realJSOP1-Sep-17 5:30
mva#realJSOP1-Sep-17 5:30 
QuestionEncrypting Connection string of a connection in an SSIS Package Pin
indian14329-Aug-17 9:45
indian14329-Aug-17 9:45 
Questionhelp SQL query Pin
trungysp198625-Aug-17 17:06
trungysp198625-Aug-17 17:06 
GeneralRe: help SQL query Pin
PIEBALDconsult25-Aug-17 19:18
professionalPIEBALDconsult25-Aug-17 19:18 
AnswerRe: help SQL query Pin
Member 133967936-Sep-17 14:58
Member 133967936-Sep-17 14:58 
QuestionConvert or use expression in Sql select statement Pin
indian14323-Aug-17 14:49
indian14323-Aug-17 14:49 
AnswerRe: Convert or use expression in Sql select statement Pin
David Mujica24-Aug-17 5:03
David Mujica24-Aug-17 5:03 
AnswerRe: Convert or use expression in Sql select statement Pin
ZurdoDev24-Aug-17 5:50
professionalZurdoDev24-Aug-17 5:50 
QuestionSSIS Package creating XML Pin
Member 1335791511-Aug-17 20:59
Member 1335791511-Aug-17 20:59 
AnswerRe: SSIS Package creating XML Pin
jschell14-Aug-17 9:28
jschell14-Aug-17 9:28 
Questionsql Query help Pin
venu6569-Aug-17 0:53
venu6569-Aug-17 0:53 
AnswerRe: sql Query help Pin
Richard MacCutchan9-Aug-17 1:19
mveRichard MacCutchan9-Aug-17 1:19 
AnswerRe: sql Query help Pin
Richard Deeming9-Aug-17 2:19
mveRichard Deeming9-Aug-17 2:19 
GeneralRe: sql Query help Pin
venu6569-Aug-17 18:54
venu6569-Aug-17 18:54 
GeneralRe: sql Query help Pin
Richard Deeming10-Aug-17 1:49
mveRichard Deeming10-Aug-17 1:49 
QuestionSQL boolean comparison Pin
User 1106097926-Jul-17 22:25
User 1106097926-Jul-17 22:25 
AnswerRe: SQL boolean comparison Pin
Richard MacCutchan26-Jul-17 23:03
mveRichard MacCutchan26-Jul-17 23:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.