Stored Procedure - usp_DimPurchaseOrderLoad

Property	Value
Name	usp_DimPurchaseOrderLoad_DupKeyCheck
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
Verify_dbo_DimPurchaseOrder	Table

Name	Type
Verify_dbo_DimPurchaseOrder	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimPurchaseOrderLoad_DupKeyCheck] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_DimPurchaseOrderLoad_DupKeyCheck]
AS
SET NOCOUNT ON
-- Create in-memory table for later use within this proc
CREATE TABLE #PurchaseOrder_Dup
    (
    [PurchaseOrderAK] VARCHAR(14) NOT NULL
    )
-- populate table with duplicate PurchaseOrderAK values
INSERT INTO #PurchaseOrder_Dup
    (
    [PurchaseOrderAK]
    )
    select PurchaseOrderAK
    from Verify_dbo_DimPurchaseOrder
    group by PurchaseOrderAK
    having COUNT(PurchaseOrderAK) > 1

--Detect duplicate records and set flag
UPDATE Verify_dbo_DimPurchaseOrder
SET RowIsDuplicate = 'Y'
FROM Verify_dbo_DimPurchaseOrder
WHERE PurchaseOrderAK
IN
(
select PurchaseOrderAK
from #PurchaseOrder_Dup
);
--Set RowIsSelected = N for duplicated records (detected above)
UPDATE RecordsToIgnore
SET RowIsSelected = 'N'
FROM dbo.Verify_dbo_DimPurchaseOrder RecordsToIgnore
INNER JOIN
(
--Returns the first record for a set of duplicated OrgUnit
--(as defined as minimum of surrogate key of temp table)
SELECT PurchaseOrderAK, MIN(VerifyPurchaseOrderSK) as Min_VerifyPurchaseOrderSK
FROM Verify_dbo_DimPurchaseOrder
WHERE PurchaseOrderAK in
  (
    select PurchaseOrderAK
    from #PurchaseOrder_Dup
  )
GROUP BY PurchaseOrderAK
) as Keeper
ON (
Keeper.PurchaseOrderAK = RecordsToIgnore.PurchaseOrderAK
AND
Keeper.Min_VerifyPurchaseOrderSK <> RecordsToIgnore.VerifyPurchaseOrderSK
)
WHERE RecordsToIgnore.RowIsDuplicate = 'Y';