none
Merge Statment Not working as expected

    Question

  • I am probably not doing this correctly but i just can't find where the issue is.

    I am getting data from ActiveDirectory, Store in a stage Table and then i use Merge Statment to compare source and destination

    If the records are matched then update the record , if not matched then insert else update the date_Delete flag.

    every time i run even in next few seconds , i get records gets marked deleted in 30-40k  number  and then create same or less or sometimes more records. I verified the uniquness it all works out to be not a problem. I just don't know why the logic don't work .

    I bring the data from 6 Active Directory Domains..

    /****** Object:  StoredProcedure [dbo].[usp_Upsert_Dim_User]    Script Date: 08/02/2013 14:56:10 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[usp_Upsert_Dim_User]
    @V_Rowcount_Insert int =0 output, @V_Rowcount_update int =0 output
    as
    SET Nocount on
    --Temp table for Mergeaction row count log
    DECLARE @rowcounts TABLE(mergeAction nvarchar(10));
    declare @insertCount int, @updateCount int, @deleteCount int;
    MERGE [Shared_RPT].[dbo].[Dim_User] AS tgt
    USING ( SELECT Distinct [EmployeeSK],[SourceSystemKey],[EmailAddress],[NetworkLoginID],[UserStatus],[EmployeeID],[Lineage_ID],[Load_DT],[LoadSequence_ID]
    ,SourceCreateDate,[Description],DN,EmailMethodType,CommonName,MailboxOverQuotaLimit 
    ,MailboxOverHardQuotaLimit ,MailboxStorageQuota,MailboxUseDefaults,
    MSRTCSIPUserEnabled ,Pwdlastset, lastLogonTimestamp, useraccountcontrol, SamaccountName FROM [ETL_Shared_RPT_Stage].[dbo].[STG_Dim_User]) AS src
    ON 
    tgt.SamAccountName =src.SamaccountName
    AND tgt.SourceSystem_Key =src.SourceSystemKey
    AND tgt.Email_Address =src.EmailAddress
    --AND tgt.Source_Create_Date=src.SourceCreateDate
    --tgt.NetworkLoginID= src.NetworkLoginID
    --  AND tgt.Source_Create_Date=src.SourceCreateDate
    --  AND tgt.[Employee_SK]=src.[EmployeeSK]
    --  AND tgt.[SourceSystem_Key]=src.[SourceSystemKey]
      --AND tgt.[Employee_ID] = Src.[EmployeeID]
     
    --  )
      
      
    ----(
    ----(Upper(Rtrim(Ltrim(tgt.Email_Address))))= (Upper(Rtrim(Ltrim(src.EmailAddress))))
    -- tgt.NetworkLoginID))))= (Upper(Rtrim(Ltrim(src.NetworkLoginID))))
    --  AND (tgt.[SourceSystem_Key])=(src.[SourceSystemKey])
    --  AND (tgt.Source_Create_Date)=(src.SourceCreateDate)
    --  AND (tgt.[Employee_SK])=(src.[EmployeeSK])
    --  AND (tgt.Common_Name)=(src.CommonName))
      
    WHEN MATCHED THEN 
    	UPDATE SET       tgt.[Employee_SK]=src.[EmployeeSK]
    					 ,tgt.[SourceSystem_Key]=src.[SourceSystemKey]
    				      ,tgt.[Email_Address]=src.[EmailAddress]
    				      ,tgt.[NetworkLoginID]=src.[NetworkLoginID]
    				     ,tgt.[User_Status]=src.[UserStatus]
    				      ,tgt.[Employee_ID]=src.[EmployeeID]
    			  	      ,tgt.[Lineage_ID]=src.[Lineage_ID]   
    					  ,tgt.[LastMod_DT]=src.[Load_DT]
    					  ,tgt.[LastModUser_NM]=suser_sname()
    					  ,tgt.Source_Create_Date =src.SourceCreateDate
    					  ,tgt.[Description] =src.[Description]
    					  ,tgt.Distinguished_Name =src.DN
    					  ,tgt.Email_Method_Type=src.EmailMethodType
    					  ,tgt.Common_Name=src.CommonName
    					  ,tgt.Mailbox_Over_Quota_Limit=src.MailboxOverQuotaLimit
    					  ,tgt.Mailbox_Over_Hard_Quota_Limit=src.MailboxOverHardQuotaLimit
    					  ,tgt.Mailbox_Storage_Quota=src.MailboxStorageQuota
    					  ,tgt.Mailbox_Use_Defaults=src.MailboxUseDefaults
    					  ,tgt.MS_RTCIP_User_Enabled_OCS=src.MSRTCSIPUserEnabled
    			     	  ,tgt.Date_Deleted = null 
    			          ,tgt.Password_Last_Set = src.Pwdlastset
    			          ,tgt.Last_Logon_Timestamp =src.lastLogonTimestamp
    			          ,tgt.User_Access_Control = src.useraccountcontrol
    			          ,tgt.SamaccountName =src.SamaccountName
    				
    		 			   
    WHEN NOT MATCHED THEN
    INSERT (
                           [Employee_SK]
    					  ,[SourceSystem_Key]
    				      ,[Email_Address]
    				      ,[NetworkLoginID]
    				      ,[Employee_ID]
    			  	      ,[Lineage_ID]
    			  	      ,[Load_DT]
    				      ,[User_Status]
                          ,[LoadSequence_ID]
                          ,[LoadUser_NM]
                          ,Source_Create_Date
    					  ,[Description] 
    					  ,Distinguished_Name
    					  ,Email_Method_Type
    					  ,Common_Name
    					  ,Mailbox_Over_Quota_Limit
    					  ,Mailbox_Over_Hard_Quota_Limit
    					  ,Mailbox_Storage_Quota
    					  ,Mailbox_Use_Defaults
    					  ,MS_RTCIP_User_Enabled_OCS
    					  ,Date_Deleted
    					  ,Password_Last_Set
    					  ,Last_Logon_Timestamp
    					  ,User_Access_Control
    					  ,SamAccountName
               
    ) VALUES
    (					   src.[EmployeeSK]
                          ,src.[SourceSystemKey]
    				      ,src.[EmailAddress]
    				      ,src.[NetworkLoginID]
    				      ,src.[EmployeeID]
    			  	      ,src.[Lineage_ID]
    			  	      ,src.[Load_DT]
    				      ,src.[UserStatus]
                          ,src.[LoadSequence_ID]
                          ,suser_sname()
                          ,src.SourceCreateDate
    					  ,src.[Description]
    					  ,src.DN
    					  ,src.EmailMethodType
    					  ,src.CommonName
    					  ,src.MailboxOverQuotaLimit
    					  ,src.MailboxOverHardQuotaLimit
    					  ,src.MailboxStorageQuota
    					  ,src.MailboxUseDefaults
    					  ,src.MSRTCSIPUserEnabled
    					  ,Null
    					  ,src.Pwdlastset
    					  ,src.lastLogonTimestamp
    					  ,src.useraccountcontrol
    					  ,src.SamaccountName
                          
                          
    )
    WHEN NOT MATCHED BY SOURCE THEN 
    		UPDATE SET  tgt.Date_Deleted=getdate()
    		,tgt.[LastMod_DT]=getdate()
            ,tgt.[LastModUser_NM]=suser_sname()
    OUTPUT $action into @rowcounts;  -- output the counts
    --get the counts to proper variable as return value
    select    @insertcount=[INSERT],
    @updatecount=[UPDATE],
    @deletecount=[DELETE]
    from    
    (       
    select mergeAction,1 rows  from    @rowcounts )p
    pivot(    count(rows)
    FOR    mergeAction IN 
    (    [INSERT], [UPDATE], [DELETE])) as pvt
    return


    ns100

    Friday, August 02, 2013 7:32 PM

All replies