How to get
Insert Count = ? Update Count = ?
in slowly changing dimension type 2 ?
Here is my procedure
CREATE PROCEDURE dbo.MergeDimCustomer
AS
BEGIN
DECLARE @CurrentDateTime datetime
DECLARE @MinDateTime datetime
DECLARE @MaxDateTime datetime
SELECT
@CurrentDateTime = cast(getdate() as datetime),
@MinDateTime = cast('1900-01-01' as datetime),
@MaxDateTime = cast('9999-12-31' as datetime)
-- SCD1
MERGE [dim].[CustomerPhone] as [target]
USING
(
SELECT
[Address],
[Id],
[Name],
[Telephone]
FROM [stg].[CustomerPhone]
) as [source]
ON
(
[source].[Id] = [target].[Id]
)
WHEN MATCHED AND
(
([target].[EndDate] = @MaxDateTime OR ([target].[EndDate] IS NULL AND @MaxDateTime IS NULL))
)
AND
(
([source].[Name] <> [target].[Name] OR ([source].[Name] IS NULL AND [target].[Name] IS NOT NULL) OR ([source].[Name] IS NOT NULL AND [target].[Name] IS NULL)) OR
([source].[Telephone] <> [target].[Telephone] OR ([source].[Telephone] IS NULL AND [target].[Telephone] IS NOT NULL) OR ([source].[Telephone] IS NOT NULL AND [target].[Telephone] IS NULL))
)
AND
(
([source].[Address] = [target].[Address] OR ([source].[Address] IS NULL AND [target].[Address] IS NULL))
)
THEN UPDATE
SET
[target].[Name] = [source].[Name],
[target].[ScdVersion] = [target].[ScdVersion] + 1,
[target].[Telephone] = [source].[Telephone]
;
-- SCD2
INSERT INTO [dim].[CustomerPhone]
(
[Address],
[EndDate],
[Id],
[Name],
[ScdVersion],
[StartDate],
[Telephone]
)
SELECT
[Address],
[EndDate],
[Id],
[Name],
[ScdVersion],
[StartDate],
[Telephone]
FROM
(
MERGE [dim].[CustomerPhone] as [target]
USING
(
SELECT
[Address],
[Id],
[Name],
[Telephone]
FROM [stg].[CustomerPhone]
) as [source]
ON
(
[source].[Id] = [target].[Id]
)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(
[Address],
[EndDate],
[Id],
[Name],
[ScdVersion],
[StartDate],
[Telephone]
)
VALUES
(
[Address],
@MaxDateTime,
[Id],
[Name],
1,
@MinDateTime,
[Telephone]
)
WHEN MATCHED AND
(
([EndDate] = @MaxDateTime OR ([EndDate] IS NULL AND @MaxDateTime IS NULL))
)
AND
(
([target].[Address] <> [source].[Address] OR ([target].[Address] IS NULL AND [source].[Address] IS NOT NULL) OR ([target].[Address] IS NOT NULL AND [source].[Address] IS NULL))
)
THEN UPDATE
SET
[EndDate] = @CurrentDateTime
OUTPUT
$Action as [MERGE_ACTION_91025df2-1344-4e4e-84f7-e790d1f77d7c],
[source].[Address] AS [Address],
@MaxDateTime AS [EndDate],
[source].[Id] AS [Id],
[source].[Name] AS [Name],
INSERTED.[ScdVersion] + 1 AS [ScdVersion],
@CurrentDateTime AS [StartDate],
[source].[Telephone] AS [Telephone]
) MERGE_OUTPUT
WHERE MERGE_OUTPUT.[MERGE_ACTION_91025df2-1344-4e4e-84f7-e790d1f77d7c] = 'UPDATE'
AND MERGE_OUTPUT.[Id] IS NOT NULL
;
END
GO
You can do it this way:
.....
THEN UPDATE
SET
[target].[Name] = [source].[Name],
[target].[ScdVersion] = [target].[ScdVersion] + 1,
[target].[Telephone] = [source].[Telephone]
OUTPUT
inserted.*,
deleted.*;
Upgrade your code based on this post: the output clause for the merge statements