-- ============== 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_day2]
-- 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
-- #############################################################################################################