July 31, 2016 at 5:58 am
Hi,
I have to do SCD2 processing, and will generally follow the process listed here:
At a high level my data is like:
record_sk: PK, identity column
record_num: An integer column that must be consistent across all closed out records based on the natural keys
key1, key2, key3: The natural keys, but repeat across SCD2 records. key1--key3 + record_current_ind='Y' will be unique, key1--key3 + record_current_ind='N' will not be unique.
My question is around record_num. The processing should be:
1) New record: record_num should equal the record_sk. So, for the very first new record, record_num = record_sk.
2) Changed record: close out old record, insert new record. I need to carry forward the record_num from the closed out record.
What do you recommend for #1 and #2? (Triggers come to mind but I want to know your thoughts re: best approach).
Thanks,
Scott
August 3, 2016 at 1:28 am
Let me restate the question then...
If I run this code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[tmp].[DF_mytable_recnum]') AND type = 'D')
BEGIN
ALTER TABLE [tmp].[mytable] DROP CONSTRAINT [DF_mytable_recnum]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[mytable]') AND type in (N'U'))
DROP TABLE [tmp].[mytable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[mytable]') AND type in (N'U'))
BEGIN
CREATE TABLE [tmp].[mytable](
[sk] [int] IDENTITY(1,1) NOT NULL,
[recnum] [int] NOT NULL,
[myvar] [varchar](1) NULL,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
[sk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[tmp].[DF_mytable_recnum]') AND type = 'D')
BEGIN
ALTER TABLE [tmp].[mytable] ADD CONSTRAINT [DF_mytable_recnum] DEFAULT ((0)) FOR [recnum]
END
GO
INSERT INTO [tmp].[mytable]
([recnum]
,[myvar])
VALUES
(DEFAULT, 'A'),
(0, 'B'),
(1, 'C')
GO
INSERT INTO [tmp].[mytable]
([myvar])
VALUES
('D')
GO
SELECT * FROM [tmp].[mytable]
What is the best way to set recnum=sk where recnum=0?
After Insert Trigger? UDF? The ideas presented here? http://www.ideaexcursion.com/2010/04/19/default-column-value-to-identity-of-different-column/
Thanks...
August 3, 2016 at 6:10 am
Simple
Select myvar,sk,recnum,
Case Recnum
When 0 Then sk Else Recnum
End as ModRecnum
From tmp.mytable
August 3, 2016 at 10:35 am
I meant automatically, as the row is created.
I'll implement this as a trigger. If there's a better approach please let me know.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply