Hi
I’m attempting use the output statement to populate the audit table for scd1 columns that are overwritten from a merge into a secondary history table –
That shows the new and old values for updated column and the rows are unpivoted to columns like below- –
From the output result
TypeTableNameClientIDSK_BirthDateSK_BirthDateOldGenderCodeGenderCodeOldClientIndClientIndOldUpdateDate
IDimClientN1968022219680222MFNN20161128
wanted results
AuditIDTypeTableNamePrimaryKeyFieldPrimaryKeyValueFieldNameOldValueNewValueUpdateDate
1UDimClientSk_ClientID1SK_BirthDate1938121419381222016-11-24 11:52:42.817
2UDimClientSk_ClientID1GenderCode193812219381212016-11-22 12:20:59.627
3UDimClientSk_ClientID1SK_BirthDate193812119381252016-11-21 12:22:24.503
---Create table
/****** Object: Table [dbo].[DimCustomerHistory] Script Date: 28/11/2016 11:31:12 ******/
CREATE TABLE dbo.SourceClient
([Sk_ClientID] [int] IDENTITY(1,1) NOT NULL,
ClientID Varchar(30)
,[Type] VARCHAR(6)
,[TableName] VARCHAR(20)
,SK_BirthDate INT
,GenderCode VARCHAR(5)
,ClientInd varchar(3)
,Rundate INT
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SourceClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 11,'UPDATE','DimClient',19381214,'M','Y',20161128)
GO
INSERT [dbo].[SourceClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 22,'UPDATE','DimClient',19480911,'F','N',20161128)
GO
INSERT [dbo].[SourceClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 33,'UPDATE','DimClient',19680222,'F','N',20161128)
GO
CREATE TABLE dbo.DimClient
([Sk_ClientID] [int] IDENTITY(1,1) NOT NULL,
ClientID Varchar(30)
,[Type] VARCHAR(6)
,[TableName] VARCHAR(20)
,SK_BirthDate INT
,GenderCode VARCHAR(5)
,ClientInd varchar(3)
,Rundate INT
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[DimClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 11,'UPDATE','DimClient',19381214,'M','Y',20161128)
GO
INSERT [dbo].[DimClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 22,'UPDATE','DimClient',19480911,'F','N',20161128)
GO
INSERT [dbo].[DimClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 33,'UPDATE','DimClient',19680222,'F','N',20161128)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimHistory](
[AuditID] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NULL,
[TableName] [varchar](128) NULL,
[PrimaryKeyField] [varchar](1000) NULL,
[PrimaryKeyValue] [varchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[UpdateDate] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[DimHistory] ON
GO
INSERT [dbo].[DimHistory] ([AuditID], [Type], [TableName], [PrimaryKeyField], [PrimaryKeyValue], [FieldName], [OldValue], [NewValue], [UpdateDate]) VALUES (1, N'U', N'DimClient', N'Sk_ClientID', N'1', N'SK_BirthDate', N'19381214', N'1938122', CAST(N'2016-11-24 11:52:42.817' AS DateTime))
GO
INSERT [dbo].[DimHistory] ([AuditID], [Type], [TableName], [PrimaryKeyField], [PrimaryKeyValue], [FieldName], [OldValue], [NewValue], [UpdateDate]) VALUES (2, N'U', N'DimClient', N'Sk_ClientID', N'1', N'GenderCode', N'1938122', N'1938121', CAST(N'2016-11-22 12:20:59.627' AS DateTime))
GO
INSERT [dbo].[DimHistory] ([AuditID], [Type], [TableName], [PrimaryKeyField], [PrimaryKeyValue], [FieldName], [OldValue], [NewValue], [UpdateDate]) VALUES (3, N'U', N'DimClient', N'Sk_ClientID', N'1', N'SK_BirthDate', N'1938121', N'1938125', CAST(N'2016-11-21 12:22:24.503' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[DimHistory] OFF
GO
---Merge
update source
set gendercode='M'
from [dbo].[SourceClient] source
where [ClientID] = 33
go
CREATE TABLE #temp
(
[Type] VARCHAR(1)
,[TableName] VARCHAR(20)
,ClientID VARCHAR(10)
,SK_BirthDate INT
,SK_BirthDateOld INT
,GenderCode VARCHAR(5)
,GenderCodeOld VARCHAR(5)
,ClientInd varchar(3)
,ClientIndOld varchar(3)
,UpdateDate INT);
MERGE INTO dbo.DimClient Target
USING dbo.SourceClient SOURCE
ON ([Target].[ClientID] = [Source].[ClientID] )
WHEN MATCHED AND (
[Target].SK_BirthDate <> [Source].SK_BirthDate
OR [Target].GenderCode <> [Source].GenderCode
OR [Target].ClientInd <> [Source].ClientInd
)
THEN UPDATE
SET [Target].[Type] =[Source].[Type]
,[Target].[TableName] =[Source].[TableName]
,[Target].[SK_BirthDate] =[Source].[SK_BirthDate]
,[Target].[GenderCode] =[Source].[GenderCode]
,[Target].[ClientInd] =[Source].[ClientInd]
,[Target].[Rundate] = cast(convert(varchar(8),GETDATE(),112) as int)
OUTPUT
CASE WHEN $action='UPDATE' THEN 'I' WHEN $action='INSERT' THEN 'I' ELSE NULL END AS [TYPE]
,'DimClient' AS TableName
,CAST(INSERTED.ClientInd AS VARCHAR(10))
,CAST(INSERTED.SK_BirthDate AS VARCHAR(15))
,CAST(DELETED.SK_BirthDate AS VARCHAR(15))
,CAST(INSERTED.GenderCode AS VARCHAR(15))
,CAST(DELETED.GenderCode AS VARCHAR(15))
,CAST(INSERTED.ClientInd AS VARCHAR(15))
,CAST(DELETED.ClientInd AS VARCHAR(15))
,cast(convert(varchar(8),GETDATE(),112) as int)
INTO #temp;
-- view Books table
SELECT *
FROM #temp
the deleted columns in the output statement contain the original value before the update.
Thanks in advance
November 28, 2016 at 7:57 pm
azdeji (11/28/2016)
HiI’m attempting use the output statement to populate the audit table for scd1 columns that are overwritten from a merge into a secondary history table –
That shows the new and old values for updated column and the rows are unpivoted to columns like below- –
From the output result
TypeTableNameClientIDSK_BirthDateSK_BirthDateOldGenderCodeGenderCodeOldClientIndClientIndOldUpdateDate
IDimClientN1968022219680222MFNN20161128
wanted results
AuditIDTypeTableNamePrimaryKeyFieldPrimaryKeyValueFieldNameOldValueNewValueUpdateDate
1UDimClientSk_ClientID1SK_BirthDate1938121419381222016-11-24 11:52:42.817
2UDimClientSk_ClientID1GenderCode193812219381212016-11-22 12:20:59.627
3UDimClientSk_ClientID1SK_BirthDate193812119381252016-11-21 12:22:24.503
---Create table/****** Object: Table [dbo].[DimCustomerHistory] Script Date: 28/11/2016 11:31:12 ******/
CREATE TABLE dbo.SourceClient
([Sk_ClientID] [int] IDENTITY(1,1) NOT NULL,
ClientID Varchar(30)
,[Type] VARCHAR(6)
,[TableName] VARCHAR(20)
,SK_BirthDate INT
,GenderCode VARCHAR(5)
,ClientInd varchar(3)
,Rundate INT
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SourceClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 11,'UPDATE','DimClient',19381214,'M','Y',20161128)
GO
INSERT [dbo].[SourceClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 22,'UPDATE','DimClient',19480911,'F','N',20161128)
GO
INSERT [dbo].[SourceClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 33,'UPDATE','DimClient',19680222,'F','N',20161128)
GO
CREATE TABLE dbo.DimClient
([Sk_ClientID] [int] IDENTITY(1,1) NOT NULL,
ClientID Varchar(30)
,[Type] VARCHAR(6)
,[TableName] VARCHAR(20)
,SK_BirthDate INT
,GenderCode VARCHAR(5)
,ClientInd varchar(3)
,Rundate INT
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[DimClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 11,'UPDATE','DimClient',19381214,'M','Y',20161128)
GO
INSERT [dbo].[DimClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 22,'UPDATE','DimClient',19480911,'F','N',20161128)
GO
INSERT [dbo].[DimClient] ([ClientID], [Type], [TableName], SK_BirthDate, GenderCode, ClientInd,Rundate) VALUES ( 33,'UPDATE','DimClient',19680222,'F','N',20161128)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimHistory](
[AuditID] [int] IDENTITY(1,1) NOT NULL,
[Type] [char](1) NULL,
[TableName] [varchar](128) NULL,
[PrimaryKeyField] [varchar](1000) NULL,
[PrimaryKeyValue] [varchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[UpdateDate] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[DimHistory] ON
GO
INSERT [dbo].[DimHistory] ([AuditID], [Type], [TableName], [PrimaryKeyField], [PrimaryKeyValue], [FieldName], [OldValue], [NewValue], [UpdateDate]) VALUES (1, N'U', N'DimClient', N'Sk_ClientID', N'1', N'SK_BirthDate', N'19381214', N'1938122', CAST(N'2016-11-24 11:52:42.817' AS DateTime))
GO
INSERT [dbo].[DimHistory] ([AuditID], [Type], [TableName], [PrimaryKeyField], [PrimaryKeyValue], [FieldName], [OldValue], [NewValue], [UpdateDate]) VALUES (2, N'U', N'DimClient', N'Sk_ClientID', N'1', N'GenderCode', N'1938122', N'1938121', CAST(N'2016-11-22 12:20:59.627' AS DateTime))
GO
INSERT [dbo].[DimHistory] ([AuditID], [Type], [TableName], [PrimaryKeyField], [PrimaryKeyValue], [FieldName], [OldValue], [NewValue], [UpdateDate]) VALUES (3, N'U', N'DimClient', N'Sk_ClientID', N'1', N'SK_BirthDate', N'1938121', N'1938125', CAST(N'2016-11-21 12:22:24.503' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[DimHistory] OFF
GO
---Merge
update source
set gendercode='M'
from [dbo].[SourceClient] source
where [ClientID] = 33
go
CREATE TABLE #temp
(
[Type] VARCHAR(1)
,[TableName] VARCHAR(20)
,ClientID VARCHAR(10)
,SK_BirthDate INT
,SK_BirthDateOld INT
,GenderCode VARCHAR(5)
,GenderCodeOld VARCHAR(5)
,ClientInd varchar(3)
,ClientIndOld varchar(3)
,UpdateDate INT);
MERGE INTO dbo.DimClient Target
USING dbo.SourceClient SOURCE
ON ([Target].[ClientID] = [Source].[ClientID] )
WHEN MATCHED AND (
[Target].SK_BirthDate <> [Source].SK_BirthDate
OR [Target].GenderCode <> [Source].GenderCode
OR [Target].ClientInd <> [Source].ClientInd
)
THEN UPDATE
SET [Target].[Type] =[Source].[Type]
,[Target].[TableName] =[Source].[TableName]
,[Target].[SK_BirthDate] =[Source].[SK_BirthDate]
,[Target].[GenderCode] =[Source].[GenderCode]
,[Target].[ClientInd] =[Source].[ClientInd]
,[Target].[Rundate] = cast(convert(varchar(8),GETDATE(),112) as int)
OUTPUT
CASE WHEN $action='UPDATE' THEN 'I' WHEN $action='INSERT' THEN 'I' ELSE NULL END AS [TYPE]
,'DimClient' AS TableName
,CAST(INSERTED.ClientInd AS VARCHAR(10))
,CAST(INSERTED.SK_BirthDate AS VARCHAR(15))
,CAST(DELETED.SK_BirthDate AS VARCHAR(15))
,CAST(INSERTED.GenderCode AS VARCHAR(15))
,CAST(DELETED.GenderCode AS VARCHAR(15))
,CAST(INSERTED.ClientInd AS VARCHAR(15))
,CAST(DELETED.ClientInd AS VARCHAR(15))
,cast(convert(varchar(8),GETDATE(),112) as int)
INTO #temp;
-- view Books table
SELECT *
FROM #temp
the deleted columns in the output statement contain the original value before the update.
Thanks in advance
hi,
Maybe this simple would help you to analyze your query
--Synchronize the target table with
--refreshed data from source table
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
OK. First things first.
Your #temp needs to change the datatype of UpdateDate
CREATE TABLE #temp (
[Type] VARCHAR(1)
, TableName VARCHAR(20)
, ClientID VARCHAR(10)
, SK_BirthDate INT
, SK_BirthDateOld INT
, GenderCode VARCHAR(5)
, GenderCodeOld VARCHAR(5)
, ClientInd VARCHAR(3)
, ClientIndOld VARCHAR(3)
, UpdateDate DATETIME
);
In your output clause, the $action[Type] for UPDATE needs to change from 'I' to 'U'
Your output for CliebtID needs to change from INSERTED.ClientInd to INSERTED.ClientID
Please specify the column names when inserting int #temp
MERGE INTO dbo.DimClient Target
USING dbo.SourceClient SOURCE
ON Target.ClientID = SOURCE.ClientID
WHEN MATCHED AND (Target.SK_BirthDate <> SOURCE.SK_BirthDate
OR Target.GenderCode <> SOURCE.GenderCode
OR Target.ClientInd <> SOURCE.ClientInd
) THEN UPDATE SET
Target.[Type] = SOURCE.[Type]
, Target.TableName = SOURCE.TableName
, Target.SK_BirthDate = SOURCE.SK_BirthDate
, Target.GenderCode = SOURCE.GenderCode
, Target.ClientInd = SOURCE.ClientInd
, Target.Rundate = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
OUTPUT CASE WHEN $action = 'UPDATE' THEN 'U' -- 'I'
WHEN $action = 'INSERT' THEN 'I'
ELSE NULL
END AS [Type]
, 'DimClient' AS TableName
, INSERTED.ClientID
, INSERTED.SK_BirthDate
, DELETED.SK_BirthDate
, INSERTED.GenderCode
, DELETED.GenderCode
, INSERTED.ClientInd
, DELETED.ClientInd
, GETDATE()
INTO #temp (
[Type]
, TableName
, ClientID
, SK_BirthDate
, SK_BirthDateOld
, GenderCode
, GenderCodeOld
, ClientInd
, ClientIndOld
, UpdateDate
);
Then you can simply UNPIVOT the data from #temp
I am assuming that you want to insert the data into dbo.DimHistory
--INSERT dbo.DimHistory ( [Type], TableName, UpdateDate, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue )
SELECT t.[Type], t.TableName, t.UpdateDate, PK.PrimaryKeyField, PK.PrimaryKeyValue, OldNew.FieldName, OldNew.OldValue, OldNew.NewValue
FROM #temp AS t
CROSS APPLY (VALUES ( 'SK_ClientID', CAST(ClientID AS VARCHAR(1000)) )
) AS PK(PrimaryKeyField, PrimaryKeyValue)
CROSS APPLY (VALUES ( 'SK_BirthDate', CAST(SK_BirthDate AS VARCHAR(1000)), CAST(SK_BirthDateOld AS VARCHAR(1000)) )
, ( 'GenderCode', CAST(GenderCode AS VARCHAR(1000)), CAST(GenderCodeOld AS VARCHAR(1000)) )
, ( 'ClientInd', CAST(ClientInd AS VARCHAR(1000)), CAST(ClientIndOld AS VARCHAR(1000)) )
) AS OldNew(FieldName, NewValue, OldValue)
WHERE PK.PrimaryKeyValue IS NOT NULL;
EDIT: Changed nvarchar to varchar
December 6, 2016 at 2:53 am
This is perfect, thank!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply