Solved

MERGE in SQL Server

Posted on 2013-12-20
7
256 Views
Last Modified: 2014-01-08
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

0
Comment
Question by:ditallop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 

Author Comment

by:ditallop
ID: 39733050
@hnasr, there isn't an odbc component to this particular issue.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39733065
Unable to reproduce the issue.

Can you help me in reproducing the tables, so to understand and test the problem
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39733925
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ditallop
ID: 39762578
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39762739
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
 

Author Comment

by:ditallop
ID: 39766901
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question