Tracking merged ids back to an active account

Check out TA_AUDIT_TRAIL -- search for [action] = 'merged' and then [old_value] = <merged account number>.  Be advised that, depending on the size of your [TA_AUDIT_TRAIL] table, you may need to optimize the query by adding a custom index.

Thanks, that works: ugly having to convert the text field, but definitely better than what I was doing.

Still think there should be a column in T_MERGED, though...

Oof. looking up by the old id is pretty ugly in that scenario (~ten seconds).

The standard merge procedure retains merged IDs in TX_CUST_KEYWORD with keyword_no = (S E L E C T keyword_no FROM T_KEYWORD WHERE description = 'Merged ID'). These attributes then naturally get transferred in any subsequent merges. You can use this on a stale dataset containing old IDs to easily update them to the current active account. For example:

WITH new AS (
  SELECT
      customer_no,
      old_customer_no = CAST(key_value AS int)
    FROM dbo.TX_CUST_KEYWORD
    WHERE keyword_no = (SELECT keyword_no FROM dbo.T_KEYWORD WHERE description = 'Merged ID')
)
SELECT
    old.customer_no,
    current_customer_no = COALESCE(new.customer_no, old.customer_no)
  FROM #old old
    LEFT JOIN new
      ON old.customer_no = new.old_customer_no;

Apologies for the bad initial post and subsequent edits. If I remove the spaces from "SELECT" in the first paragraph, the post fails with an error. What even!?

Another place, I guess.  Haven't tried, but i guess even though it's still a text search, at least it's a smaller table overall than TA_AUDIT_TRAIL, and you can limit the search space by numeric id instead of another text label.

Being able to limit the search space by numeric ID instead of text isn't the determining factor here — it's that the TX_CUST_KEYWORD table has an index on keyword_no. TA_AUDIT_TRAIL only has indexes on customer_no and date, so you have to do a full table scan every time you want to filter to [action] = 'merged'. If TA_AUDIT_TRAIL had an index on action, the performance would be (more or less) the same.

The performance of the join between old and new could be further improved if the "new" table had an index on "old_customer_no". That could be accomplished by doing the select/cast from TX_CUST_KEYWORD into a temp table with that index instead of in a CTE.

Ugh, I was afraid of this: my "Merged ID" keywords match T_MERGED in count, but don't seem to match up to the current customer -> all past merged ids table I built.  Haven't figured out what the discrepancy means at the moment.

I don't think I ever mentioned the use case: we share our contribution information with a campus development system of record, and the biggest challenge is constituent matching.  We have some processes for trying to update our customer accounts with new campus system ids when those change (i.e. are merged), while I'm working on building a custom API endpoint to help them update their system with correct Tessitura ids.  I'd want them to be able to enter a Tessitura Id and either confirm it or return the new id that it was merged into.

It seems like you are averse to using a loop because of the length of time it might take to resolve that in active use, is that correct?  This might not be the most elegant solution ever, but would this work?

• Create a local data table with two columns, old/original_customer_no and merged_customer_no.
• Populate it initially with all existing customer_nos, with those customer_nos in both columns.
• Set up a nightly procedure to insert the day's new customer_nos into the table.
• Run a one time, loop-laden/data intensive update to that table updating the merged_customer_no column to final end result customer_no after all successive merges.
• Then create a nightly procedure which uses the data from your merge process and updates the requisite customer_nos for that night's merges.
  • This would need to be a far less intensive process since what will happen is you have, say customers 1, 2, 3, 4 and 5 all of whose merged_customer_no is 5.  So your update process just searches for all merged_customer_nos = 5 and change them to 6 when 5 gets merged to 6.
• Then it is used kind of like how you would use V_CUSTOMER_WITH_HOUSEHOLD.  You put in the customer_no, it finds the old/original_customer_no and then returns the merged_customer_no.

Again, not exactly elegant, but as long as you do the full data dump up front, and set a couple of procedures to take care of the nightly work, it should get you what you want, no?  I mean, if we assume that you can keep your local table up to date, calling that table should be a very quick return.

Yeah, making my own table was pretty much what I wanted to avoid, but I'll have to consider it.  Keeping it up would be a simple matter of a line in LP_CONST_MERGE.

I almost suggested putting that update in LP_CONST_MERGE, but I wanted to leave the window open in case you were worried that the merge would fail for some other reason.  In which case, the already updated information would need to be rolled back.  Though there are of course ways to solve that issue, too.

No need to worry about that:

This procedure
is actually called twice during the procedure, once before any other data is merged and once after
the final step in the merge which is when the T_CUSTOMER row for the deleted id is marked Merged.
Both calls are inside the merge transaction and will be rolled back if any error occurs. Likewise,
if this procedure raises an error, then the entire merge process will be rolled back and the next
constituent merge will begin. Failed merges are noted in the merge error log.

Fair enough then.  We happen to actually not have anything in either LP_VALIDATE_CUST_MERGE or LP_CONST_MERGE, so I cannot say have paid extremely close attention to when they are called beyond knowing that they are called to validate the merge and during the merging process.  But good to know.

It works pretty nicely.  I've pulled out our custom code there into one function and two stored procedures.  It's hard to remember, but certainly any time you add a custom table that references customers you'll want to add an action in there.

IF @merge_stage = 'B'
  BEGIN

	select @errmsg = dbo.LF_CPSMA_CONST_MERGE_PRECHECK(@kept_id, @deleted_id)
	if(@errmsg <> '')
	begin
		select @err_table = @errmsg
		goto GiveErrMsg
	end

	exec @return_status = dbo.LP_CPSMA_CONST_MERGE_PREOP @kept_id, @deleted_id, @errmsg OUTPUT
	
	if(@return_status <> 0)
	begin
		select @err_table = @errmsg
		goto GiveErrMsg
	end

END --@merge_stage = 'B'

If @merge_stage = 'A'
BEGIN
	
	exec @return_status = dbo.LP_CPSMA_CONST_MERGE_POSTOP @kept_id, @deleted_id, @errmsg OUTPUT
	
	if(@return_status <> 0)
	begin
		select @err_table = @errmsg
		goto GiveErrMsg
	end


END --@merge_stage = 'A'

Hi all,

I know this is an older post, but I thought some of you might find this useful for this and possibly other purposes. A recursive CTE can be used in situations like this. Here is code that will always return the most recent kept_id from t_merged regardless of the which delete_id is passed in the chain:

    

        Fullscreen
        6443.merged_cte.sql
        Download
    
    
DECLARE @delete_id int = 355941;

WITH merged_cte (kept_id)
AS (
    SELECT a.kept_id
    FROM dbo.T_MERGED a
    WHERE a.delete_id = @delete_id
          AND a.status = 'S'
    UNION ALL
    SELECT a.kept_id
    FROM dbo.T_MERGED a
        INNER JOIN merged_cte b
            ON a.delete_id = b.kept_id
    WHERE a.status = 'S'
       )
SELECT a.kept_id
FROM merged_cte a
WHERE NOT EXISTS -- Make sure we only get the most recent kept_id in the chain
(
    SELECT *
    FROM dbo.T_MERGED b
    WHERE a.kept_id = b.delete_id
          AND b.status = 'S'
);

Here are some rows from our T_MERGED table that I tested against: 

As you can see, the merge chain looks like this: 355941 --> 119157 --> 57053 --> 3465887 

I can set @delete_id to the above query to 355941, 119157, or 57053 and it will always return 3465887. And it runs in under a second. I hope this helps! 

Thanks,
David