我们可以从缓慢变化的维度类型2存储过程中获得INSERT UPDATE Count吗?

Question

如何获取

Insert Count = ? Update Count = ?

在缓慢变化的维度类型2 ?

步骤如下

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

Answer 1

你可以这样做:

.....
THEN UPDATE
SET
[target].[Name] = [source].[Name],
[target].[ScdVersion] = [target].[ScdVersion] + 1,
[target].[Telephone] = [source].[Telephone]

OUTPUT
inserted.*,
deleted.*;

根据这篇文章升级你的代码:merge语句的output子句