-- begin of insert using merge
insert into dbo.tblDimSCDType2Example
( --Table and columns in which to insert the data
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum,
EffectiveDate,
EndDate
)
-- Select the rows/columns to insert that are output from this merge statement
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).
select
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum,
EffectiveDate,
EndDate
from
(
-- This is the beginning of the merge statement.
-- The target must be defined, in this example it is our slowly changing
-- dimension table
MERGE into dbo.tblDimSCDType2Example AS target
-- The source must be defined with the USING clause
USING
(
-- The source is made up of the attribute columns from the staging table.
SELECT
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum
from dbo.tblStaging
) AS source
(
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum
) ON --We are matching on SourceID1 and SourceID2 in the target table and the source table.
(
target.SourceID1 = source.SourceID1
and target.SourceID2 = source.SourceID2
)
-- If the ID's match but the CheckSums are different, then the record has changed;
-- therefore, update the existing record in the target, end dating the record
-- and set the CurrentRecord flag to N
WHEN MATCHED and target.Check_Sum <> source.Check_Sum
and target.CurrentRecord='Y'
THEN
UPDATE SET
EndDate=getdate()-1,
CurrentRecord='N',
LastUpdated=getdate(),
UpdatedBy=suser_sname()
-- If the ID's do not match, then the record is new;
-- therefore, insert the new record into the target using the values from the source.
WHEN NOT MATCHED THEN
INSERT
(
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum
)
VALUES
(
source.SourceID1,
source.SourceID2,
source.Attribute1,
source.Attribute2,
source.Check_Sum
)
OUTPUT
$action,
source.SourceID1,
source.SourceID2,
source.Attribute1,
source.Attribute2,
source.Check_Sum,
getdate(),
'12/31/9999'
)
-- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes
(
action,
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum,
EffectiveDate,
EndDate
)
where action='UPDATE';
Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator