MERGE in SQL Server

Techies--

Will someone explain to me why this MERGE statement is setting the RecordIsDeleted flag to 1 on every row? Obviously NOT MATCHED by SOURCE trips it-- but I thought the criteria for the MATCH is on the 2 keys under the ON condition--not every column after the ON, which is what it appears to be doing.  Any advice on how to correct this?



MERGE dimension.CustomerSegment  AS TARGET

	-- SELECT ALL COLUMNS IN THE SOURCE ETL TABLE
	USING ( SELECT DS.SegmentKey
				 , DC.CustomerKey
				 , DCS.[MemberInternalKey]
				 , DCS.[BuyingUnitInternalKey]
				 , DCS.[SegmentInternalKey]
				 , DCS.[MemberDK]
				 , DCS.[BuyingUnitDK]
				 , DCS.[SegmentId]
				 , DCS.[SegmentDescription]
				 , DCS.[MatrixMemberId]
				 , DCS.[ClubInternalKey]
				 , DCS.[UpdatedDate]
				 , DCS.[StartDate]
				 , DCS.[EndDate]
				 , DCS.[AttachmentSourceId]
				 , DCS.[AttachmentSourceName] 
				 , DCS.[RecordFromDate]
				 , DCS.[RecordToDate]
				 , DCS.[RecordIsCurrent]
				 , DCS.[RecordisDeleted]        
			  FROM [etl].[CustomerSegment]		DCS
			 INNER JOIN dimension.Customer		 DC		ON DC.MemberDK				=	DCS.MemberDK
													   AND DC.BuyingUnitDK			=	DCS.BuyingUnitDK
			 INNER JOIN dimension.Segment		 DS		ON DS.SegmentInternalKey	=	DCS.SegmentInternalKey

          ) AS Source
                 ( SegmentKey
				 , CustomerKey
				 , [MemberInternalKey]
				 , [BuyingUnitInternalKey]
				 , [SegmentInternalKey]
				 , [MemberDK]
				 , [BuyingUnitDK]
				 , [SegmentId]
				 , [SegmentDescription]
				 , [MatrixMemberId]
				 , [ClubInternalKey]
				 , [UpdatedDate]
				 , [StartDate]
				 , [EndDate]
				 , [AttachmentSourceId]
				 , [AttachmentSourceName]
				 , [RecordFromDate]
				 , [RecordToDate]
				 , [RecordIsCurrent]
				 , [RecordisDeleted]
				 )
		ON (    Source.CustomerKey		= Target.CustomerKey		-- Key will be customer, segment, and segment start date.	
		         AND Source.SegmentKey	= Target.SegmentKey			-- Remember that segments can come and go
			
	

		   )

		-- NO MATCH IN TARGET MEANS NEW DATA - PERFORM AN INSERT
		WHEN NOT MATCHED BY TARGET THEN
	    INSERT (   [SegmentKey]
				  ,[CustomerKey]
				  ,[UpdatedDate]
				  ,[StartDate]
				  ,[EndDate]
				  ,[AttachmentSourceId]
				  ,[AttachmentSource]
				  ,[RecordFromDate]
				  ,[RecordToDate]
				  ,[RecordIsCurrent]
				  ,[RecordisDeleted]
				  ,[InsertedBy]
				  ,[InsertedDate]
				  ,[ModifiedBy]
				  ,[ModifiedDate]
		      )
        VALUES
             (   Source.[SegmentKey]
				,Source.[CustomerKey]
				,Source.[UpdatedDate]
				,Source.[StartDate]
				,Source.[EndDate]
				,Source.[AttachmentSourceId]
				,Source.[AttachmentSourceName]
				,Source.[RecordFromDate]
				,Source.[RecordToDate]
				,Source.[RecordIsCurrent]
				,Source.[RecordisDeleted]
			    ,@ExecutedBy						-- InsertedBy
			    ,GetDate()						    -- InsertedDate
			    ,@ExecutedBy						-- ModifiedBy
			    ,GetDate()						    -- ModifiedDate
		     )
		-- NO MATCH IN SOURCE MEANS WHAT SEGMENT DID EXIST FOR MEMBER IS NO LONGER CURRENT
		-- *Note: Because deleting/fragmenting indexes can be a performance killer, the row wil
		--        be flagged as RecordIsDeleted to TRUE.

		WHEN NOT MATCHED BY SOURCE THEN
		 UPDATE	
		   SET RecordIsDeleted		=	1   -- Set for future delete/reindex maint job.

		WHEN MATCHED			
         THEN UPDATE	
			SET Updateddate				=	Source.[Updateddate]		
			,StartDate					=	Source.[StartDate]			
			,EndDate					=	Source.[EndDate]
			,AttachmentSourceId			=	Source.[AttachmentSourceId]
			,AttachmentSource			=	Source.[AttachmentSourceName]
			,RecordToDate				=	Source.[RecordToDate]
			,[RecordIsCurrent]			=	Source.[RecordIsCurrent]
			,[RecordIsDeleted]			=	Source.[RecordIsDeleted]  
			,ModifiedBy					=	@ExecutedBy						-- ModifiedBy
			,ModifiedDate				=	GetDate()						--  ModifiedDate	


 ; -- END OF THE MERGE STATEMENT

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Ditallop,

It looks like a data issue to me.  When you inner join the 3 tables to produce [source] are you sure that you're getting the rows that you expect?

I created a small test case that closely resembles your example.  It works just fine.


Kent


CREATE TABLE dbo.t1 (k1 integer, k2 integer, RecordIsDeleted integer, Text varchar (100));
CREATE TABLE dbo.t2 (k1 integer, k2 integer, Text varchar (100));

DELETE FROM dbo.t1;
DELETE FROM dbo.t2;

INSERT INTO dbo.t1 SELECT 1, 101, 0, 'A';
INSERT INTO dbo.t1 SELECT 2, 102, 0, 'B';
INSERT INTO dbo.t1 SELECT 3, 103, 0, 'C';
INSERT INTO dbo.t1 SELECT 4, 104, 0, 'D';
INSERT INTO dbo.t2 SELECT 1, 201, 'E';
INSERT INTO dbo.t2 SELECT 2, 102, 'F';
INSERT INTO dbo.t2 SELECT 5, 103, 'G';
INSERT INTO dbo.t2 SELECT 6, 204, 'H';

select * from t1;
select * from t2;

MERGE dbo.t1 AS [target]
USING 
( 
  SELECT k1, k2, Text
  FROM t2
) as [source] (k1, k2, text)
  ON ([source].k1 = [target].k1
 AND [source].k2 = [target].k2)
WHEN NOT MATCHED BY TARGET THEN
  INSERT (k1, k2, RecordIsDeleted, Text) 
    VALUES ([source].k1, [source].k2, 0, [source].text)
WHEN NOT MATCHED BY SOURCE THEN
 UPDATE	SET RecordIsDeleted		=	1   -- Set for future delete/reindex maint job.
WHEN MATCHED			
  THEN UPDATE	
    SET Text = [source].Text;
                        
                        
select * from t1;
select * from t2;

Open in new window


The results are:

t1 before the merge:
1      101      0      A
2      102      0      B
3      103      0      C
4      104      0      D

t2 before the merge:
1      201      E
2      102      F
5      103      G
6      204      H

t1 after the merge:
1      101      1      A
2      102      0      F
3      103      1      C
4      104      1      D
5      103      0      G
6      204      0      H
1      201      0      E

t2 after the merge:
1      201      E
2      102      F
5      103      G
6      204      H
0
 
Paula DiTalloIntegration developerAuthor Commented:
@hnasr, there isn't an odbc component to this particular issue.
0
 
hnasrCommented:
Unable to reproduce the issue.

Can you help me in reproducing the tables, so to understand and test the problem
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Paula DiTalloIntegration developerAuthor Commented:
Maddening isn't it? This is a perfectly good merge statement that should work--but it just plain doesn't with the live data--I've had to give up on this approach and go the update/insert/delete route... due to time constraints.  Thanks for looking at this.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
My hunch still revolves around a data issue.  I'll be glad to keep working with you if you want to dig some more.
0
 
Paula DiTalloIntegration developerAuthor Commented:
Thanks Kdo--now that the immediate need to solve this has past, I'll resurrect the what I have and post a test script
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.