The platform our church uses to help us manage information and relationships has a great feature to merge duplicate records. Unfortunately, this feature causes a new ID to be generated for a person. That can make is difficult to integrate with other custom features, systems, etc. I wrote this simple function to keep person links between integrated systems no matter what.
How To USE.
Add [dbo].[cust_fn_getcurrent_person_id](YourPersonIDInCustTable) in your join clause. Comment if you would like more explanation on how to use.
/****** Object: UserDefinedFunction [dbo].[cust_fn_getcurrent_person_id] Script Date: 11/1/2014 9:21:12 AM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE function [dbo].[cust_fn_getcurrent_person_id](@CCPID int)
DECLARE @PersonID as int
SET @PersonID =
(Select Top 1 lt.current_person_id From
(SELECT cpm.new_person_id AS current_person_id, cpm.old_person_id AS old_person_id
FROM core_person_merged cpm INNER JOIN
core_person cp ON cpm.new_person_id = cp.person_id
SELECT cp2.person_id AS current_person_id, cp2.person_id AS old_person_id
FROM core_person cp2) LT
Where lt.old_person_id = @CCPID)