Updated with work-around examples for "
Unable to perform Slowly Changing Dimension updates on a dimension table with a foreign key"
-- MERGE into tables with FK Constraints: how to MERGE Workaround examples:
-- see below sql examples
/****************************************
-- Temp Table Example
*****************************************/
/****************************************
-- Drop Check Constraint Examples
*****************************************/
-- Code:
/****************************************
-- Temp Table Example
*****************************************/
declare @Extract_Datetime datetime, @Expire_Datetime datetime, @auditKey uniqueidentifier
Select @Extract_Datetime = getdate()
, @Expire_Datetime = CoReStage.dbo.udfGetExpireDatetimeDefault()
,@auditKey = '2583EA74-70EF-E211-AE25-00090FFE0001'
select* into #Material_Status from [Material_Status] where 1=0
INSERT INTO #Material_Status ([Material_Status_UID],[Code],[Description],[Effective_Datetime],[Expire_Datetime],[IsCurrent],[Audit_Key])
SELECT Material_Status_UID,[Code],[Description], [Effective_Datetime], [Expire_Datetime], [IsCurrent], [Audit_Key]
FROM
(
MERGE [Material_Status] as targ
USING material_status AS src
ON (targ.[Code] = src.[Code])
--WHEN MATCHED and checksum(targ.[Description],targ.[code]) <> checksum(src.[Description],src.[code]) and targ.IsCurrent='1'THEN
WHEN MATCHED and (targ.[Description] <> src.[Description] and targ.IsCurrent='1') THEN
UPDATE SET targ.IsCurrent = '0', targ.[Expire_Datetime] = @Extract_Datetime
WHEN NOT MATCHED THEN
/********** NEW ROW ***********/
INSERT VALUES (newid(), src.[Code], src.[Description], @Extract_Datetime, @Expire_Datetime, '1', @auditKey)
OUTPUT $action Action_Out, inserted.Material_Status_UID, src.[Code], src.[Description], @Extract_Datetime [Effective_Datetime] , @Expire_Datetime Expire_Datetime, '1' IsCurrent, @auditKey [Audit_Key]
) AS Changes
WHERE Changes.Action_Out = 'UPDATE';
select * from #Material_Status
insert into [Material_Status] ([Code],[Description],[Effective_Datetime],[Expire_Datetime],[IsCurrent],[Audit_Key])
select [Code],[Description],[Effective_Datetime],[Expire_Datetime],[IsCurrent],[Audit_Key] from #Material_Status
drop table #Material_Status
select * from [Material_Status]
select * from material_status
/****************************************
-- Drop Check Constraint Examples
*****************************************/
-- 1) Create tables
USE [AdventureWorks2012]
GO
CREATE TABLE [dbo].[Supplier](
[SupplierCode] CHAR(8) PRIMARY KEY,
[SupplierName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Supplier] ([SupplierCode], [SupplierName], [Address])
VALUES
('S0000001', 'ABC Company', 'USA'),
('S0000002', 'XYZ Corporation', 'USA')
GO
SELECT * FROM [dbo].[Supplier]
USE [AdventureWorks2012]
GO
CREATE TABLE [dbo].[DimSupplier](
[SupplierId] [int] IDENTITY(1,1) NOT NULL,
[SupplierCode] CHAR(8),
[SupplierName] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[EffectiveDate] [date] NULL,
[ExpirationDate] [date] NULL,
[CurrentFlag] [char](1) NULL,
CONSTRAINT [PK_DimSupplier] PRIMARY KEY CLUSTERED ([SupplierId] ASC)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DimSupplierAttributes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DimSupplierID] [int] NOT NULL,
CONSTRAINT [PK_DimSupplierAttributes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [DimSupplierAttributes] WITH CHECK ADD CONSTRAINT [R_10355] FOREIGN KEY([DimSupplierID])
REFERENCES [DimSupplier] ([SupplierId])
GO
select * from [dbo].[DimSupplier]
select * from [dbo].[Supplier]
--2) Type 1 changes
MERGE INTO [dbo].[DimSupplier] Dest
USING [dbo].[Supplier] Src
ON (Dest.[SupplierCode] = Src.[SupplierCode])
WHEN MATCHED AND (Dest.[Address] != Src.[Address])
THEN UPDATE
SET Dest.[Address] = Src.[Address];
select * from [dbo].[DimSupplier]
--3) Type 2 changes (creates error)
-- This inserts another record to the dimension for SCD Type changes
INSERT INTO [dbo].[DimSupplier] ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
SELECT [SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag]
FROM
(
MERGE [dbo].[DimSupplier] TT
USING [dbo].[Supplier] ST
ON (TT.[SupplierCode] = ST.[SupplierCode])
-- This inserts new records in the dimension table
WHEN NOT MATCHED THEN
INSERT ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
VALUES ([SupplierCode], [SupplierName], [Address], '01/01/1900', NULL, 'Y')
-- This marks the older record to be outdated for SCD Type 2
WHEN MATCHED AND TT.[CurrentFlag] = 'Y' AND (ISNULL(TT.[SupplierName], '') != ISNULL(ST.[SupplierName], '')) THEN
UPDATE SET TT.[CurrentFlag] = 'N', TT.[ExpirationDate] = GETDATE() - 1
OUTPUT $Action Action_Taken, ST.[SupplierCode], ST.[SupplierName],
ST.[Address], GETDATE() AS [EffectiveDate], NULL AS [ExpirationDate], 'Y' AS [CurrentFlag]
)
AS MERGE_OUT
WHERE MERGE_OUT.Action_Taken = 'UPDATE';
GO
SELECT * FROM [dbo].[DimSupplier]
-- type 2 change
USE [AdventureWorks2012]
GO
UPDATE [dbo].[Supplier]
SET [SupplierName] = 'ABC Company LtdZ.'
WHERE [SupplierCode] = 'S0000001'
GO
SELECT * FROM [dbo].[Supplier]
GO
SELECT * FROM [dbo].[DimSupplier]
-- exec type 2 merge above
SELECT * FROM [dbo].[Supplier]
GO
SELECT * FROM [dbo].[DimSupplier]
--4) Type 2 changes (fix error with NOCHECK CONSTRAINT)
-- MERGE with Drop FK on all Child tables
ALTER TABLE [dbo].[DimSupplierAttributes] NOCHECK CONSTRAINT ALL
GO
INSERT INTO [dbo].[DimSupplier] ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
SELECT [SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag]
FROM
(
MERGE [dbo].[DimSupplier] TT
USING [dbo].[Supplier] ST
ON (TT.[SupplierCode] = ST.[SupplierCode])
-- This inserts new records in the dimension table
WHEN NOT MATCHED THEN
INSERT ([SupplierCode], [SupplierName], [Address], [EffectiveDate], [ExpirationDate], [CurrentFlag])
VALUES ([SupplierCode], [SupplierName], [Address], '01/01/1900', NULL, 'Y')
-- This marks the older record to be outdated for SCD Type 2
WHEN MATCHED AND TT.[CurrentFlag] = 'Y' AND (ISNULL(TT.[SupplierName], '') != ISNULL(ST.[SupplierName], '')) THEN
UPDATE SET TT.[CurrentFlag] = 'N', TT.[ExpirationDate] = GETDATE() - 1
OUTPUT $Action Action_Taken, ST.[SupplierCode], ST.[SupplierName],
ST.[Address], GETDATE() AS [EffectiveDate], NULL AS [ExpirationDate], 'Y' AS [CurrentFlag]
)
AS MERGE_OUT
WHERE MERGE_OUT.Action_Taken = 'UPDATE';
GO
SELECT * FROM [dbo].[DimSupplier]
ALTER TABLE [dbo].[DimSupplierAttributes] WITH NOCHECK CHECK CONSTRAINT ALL
GO
