MERGE	Member.factMessageRestriction AS tgt
USING	(
		SELECT		mmrCustomerID,
				mmrMessageTypeID,
				MAX(mmrLastKnownDate) AS mmrLastKnownDate
		FROM		StageArea.MessageRestrictions
		GROUP BY	mmrCustomerID,
				mmrMessageTypeID
	) AS src ON src.mmrCustomerID = tgt.mmrCustomerID
		AND src.mmrMessageTypeID = tgt.mmrMessageTypeID
WHEN	MATCHED AND tgt.mmrLastKnownDate < src.mmrLastKnownDate
		THEN	UPDATE
			SET	tgt.mmrLastKnownDate = src.mmrLastKnownDate,
				tgt.mmrStatus = 'Renewed'
WHEN	NOT MATCHED BY SOURCE
		THEN	UPDATE
			SET	tgt.mmrStatus = 'Removed'
--WHEN	NOT MATCHED BY SOURCE
--		THEN	DELETE
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					mmrCustomerID,
					mmrMessageTypeID,
					mmrLastKnownDate,
					mmrStatus
				)
			VALUES	(
					src.mmrCustomerID,
					src.mmrMessageTypeID,
					src.mmrLastKnownDate,
					'New'
				);

-- Will get you in trouble
MERGE	Member.factMessageRestriction AS tgt
USING	(
		SELECT		mmrCustomerID,
				mmrMessageTypeID,
				MAX(mmrLastKnownDate) AS mmrLastKnownDate
		FROM		StageArea.MessageRestrictions
		GROUP BY	mmrCustomerID,
				mmrMessageTypeID
	) AS src ON src.mmrCustomerID = tgt.mmrCustomerID
		AND src.mmrMessageTypeID = 1

-- Proper workaround
MERGE	Member.factMessageRestriction AS tgt
USING	(
		SELECT		mmrCustomerID,
				mmrMessageTypeID,
				MAX(mmrLastKnownDate) AS mmrLastKnownDate
		FROM		StageArea.MessageRestrictions
		WHERE		mmrMessageTypeID = 1
		GROUP BY	mmrCustomerID,
				mmrMessageTypeID
	) AS src ON src.mmrCustomerID = tgt.mmrCustomerID

MERGE INTO #changeRows AS cr
USING (
SELECT
	pw.[ownerPersonIdentityID] AS [personIdentityID]
	, pw.[personId] AS [personId]
	, pw.[effective] AS [effective]
	, pw.[initiated] AS [initiated]
	, bens.[selectionID] AS [selectionID]
	, sc.[ID] AS [selectionComponentID]
FROM
	#personWorkspace pw

	-- Limit rows employee in a valid payroll window
	JOIN #empPayrollMap epm ON
		epm.[personIdentityID] = pw.[ownerPersonIdentityId]
		AND epm.[effectiveFrom] <= pw.[effective]
		AND epm.[effectiveTo] >= pw.[effective]

	-- get the benefits effective at this time
	CROSS APPLY dbo.RPT_FN_selectionsForPersonIdentityID (
		pw.[typ]
		, pw.[ownerPersonIdentityID]
		, pw.[effective]
		, pw.[initiated]
		, @reportToDate
		)
		AS bens

	-- limit rows to benefits we are reporting on
	JOIN #reportBens rb ON rb.[benefitID] = bens.[benefitID]

	-- A personal Details change for employee effects all selectionComponentes
	JOIN selectionComponent sc ON sc.[selectionID] = bens.[selectionID]
					
	-- Join to person to base comparison on
	JOIN person p ON p.[ID] = pw.[personID]

	-- Join the previous relevent row in #personWorkspace
	JOIN #personWorkspace pw2 ON
		pw2.[personIdentityID] = pw.[personIdentityID]
		AND pw2.[reportLevel] = pw.[reportLevel] - 1

	-- 2nd join to person to compare to
	JOIN person p2 ON p2.[ID] = pw2.[personID]
WHERE
	-- Only lines that are effective after the from DATETIME
	pw.[effective] >= @reportFromDate

	-- Dynamic Substitution of checked columns here
	AND (
		1 = 0
		OR ( p.[typ] = 'Employee' AND  p.[address1] <> p2.[address1] )
	)
)
AS pd ON
	pd.[personIdentityID] = cr.[personIdentityID]
	AND pd.[effective] = cr.[effective]
	AND pd.[selectionComponentID] = cr.[selectionComponentID]

-- Found a match? Great -- update the info with new personID etc
WHEN MATCHED THEN
	UPDATE
	SET
		cr.[initiated] = pd.[initiated]
		, cr.[personId] = pd.[personId]
		, cr.[changeType] = cr.[changeType] + ' / Personal Details change'

-- No match? Insert a Personal Details Change Row
WHEN NOT MATCHED THEN
	INSERT (
		[personIdentityID]
		, [personID]
		, [effective]
		, [initiated]
		, [selectionID]
		, [selectionComponentID]
		, [previousSelectionID]
		, [previousSelectionComponentID]
		, [changeType]
		)
	VALUES (
		pd.[personIdentityID]
		, pd.[personId]
		, pd.[effective]
		, pd.[initiated]
		, pd.[selectionID]
		, pd.[selectionComponentID]
		, pd.[selectionID]
		, pd.[selectionComponentID]
		, 'Personal Details Change'
		);

quote:

---

+=

---

quote:

---

BY TARGET

---

quote:

---

Originally posted by j4ydh

Hi,

I recently had help from the SQL TEAM forum to create an insert sproc that only added items that didn't already exist.

@name varchar (50),
@DT smalldatetime,
@cam int = 1

AS

INSERT INTO x_b([name] ,DT ,cam)
SELECT @name, @DT, @cam
WHERE NOT EXISTS
(
SELECT *
FROM x_b xb1 WITH (SERIALIZABLE) -- apply key range lock
WHERE xb1.[name] = @name
)

My new concern is performance over time and how to prepare either my page code or addition to the sproc when the database items carry hundreds of thousands of entries.

Two concerns:

1) If a large amount of additions are being added at once.. best way to prepare?
2) If a large amount of items already exist in the database.. the best way to prepare?

Or am I worrying for no reason?

Thanks in advance
J

---