Example Stage (day 1): Step-by-step Script

-- ============== Start load data from sourcetable to anchor model ==========================

-- First create table from sourcetable with an uniqe key ====================================
-- the key is defined by the source system ==================================================
-- in this case, location is the business key. A stage may change name, but not location. ===

SELECT  [name] as StageName,
    [address] as StageAddress,
    NULL AS ST_ID_Known,
    NULL AS ST_ID,
    [address] AS _key

INTO    #StageTable

FROM    [Stages_raw_day1]



-- Identify all stages that allready exists in the Anchor Database ===========================

UPDATE T
SET     T.ST_ID_Known=1,
    T.ST_ID=lST.ST_ID


FROM lST_Stage lST
    INNER JOIN #StageTable T ON (lST.[ST_LOC_Stage_Location]=T._key)


-- Create new Anchors for all new Stages ======================================================

DECLARE @ids table (ST_ID int)
DECLARE @NoOfKeys int
DECLARE @Metadata_ID int

SET @NoOfKeys=0
SET @Metadata_ID =0
SELECT @NoOfKeys= COUNT(distinct _key) from #StageTable WHERE ST_ID_Known IS NULL

-- If we have new stages that should be created ===============================================

IF @NoOfKeys>0
    BEGIN
             -- Generate anchors
               INSERT INTO @ids
               EXEC kST_Stage @NoOfKeys,@Metadata_ID


               -- Give each new anchor id to a coresponding new stage row

                UPDATE  src

                SET     src.ST_ID=tmpids.ST_ID,
                        src.ST_ID_Known=0

                FROM (
                         -- Every new record gets a _key value  (duplicates gets the same KeyNumber) (if we have history)
                         SELECT DENSE_RANK () OVER (ORDER BY _key) as KeyNumber,*
                         FROM #StageTable
                         WHERE ST_ID IS NULL
                        )  src

               LEFT OUTER JOIN
                          (
                          SELECT
                          ROW_NUMBER() OVER (ORDER BY ST_ID) as KeyNumber,
                          ST_ID

                          FROM @ids
                          ) tmpids ON (src.KeyNumber=tmpids.KeyNumber)

    END


-- Add attributes for the generated new anchors ================================================

INSERT INTO [dbo].[lST_Stage]
           ([ST_ID]
           ,[Metadata_ST]
           ,[ST_NAM_Stage_Name]
           ,[ST_NAM_ChangedAt]
           ,[Metadata_ST_NAM]
           ,[ST_LOC_Stage_Location]
           ,[Metadata_ST_LOC])



SELECT   T.ST_ID,
     @Metadata_ID,
     T.StageName,
     GETDATE(),
     @Metadata_ID,
     T.StageAddress,
     @Metadata_ID

FROM #StageTable T
WHERE T.ST_ID_Known=0


-- Change attributes for anchors where attributes differ from source (eg namechange for a stage)


INSERT INTO [dbo].[lST_Stage]
           ([ST_ID]
           ,[Metadata_ST]
           ,[ST_NAM_Stage_Name]
           ,[ST_NAM_ChangedAt]
           ,[Metadata_ST_NAM])


SELECT  T.ST_ID,
     @Metadata_ID,
     T.StageName,
     GETDATE(),
     @Metadata_ID


FROM    [dbo].[lST_Stage] lST
    LEFT OUTER JOIN #StageTable T ON (lST.ST_ID=T.ST_ID)
WHERE   lST.[ST_NAM_Stage_Name]<>T.StageName
AND T.ST_ID_Known=1

DROP TABLE #StageTable