Flag the old column to 0 when new record is inserted!

Question

Flag the old column to 0 when new record is inserted!

hegdesuchi

Hall of Fame

Points: 3047
More actions
December 1, 2015 at 4:49 pm

#399411

Hi All,
I have a scenario where I have to update the old ID value to 0 and new one to 1.
For example,
my ODS table will have below values:
ID Value Date
1 "abc" 1/1/1900
2 "efh" 1/1/1900
3 "xyz" 1/1/1900
ODS history:
ID Value Date Flag Changed_Date
1 "abc" 1/1/1900 1 01/12/2015
2 "efh" 1/1/1900 1 01/12/2015
3 "xyz" 1/1/1900 1 01/12/2015
Now My ODS will have changed value for ID=1
ID Value Date
1 "mno" 1/1/1900
2 "efh" 1/1/1900
3 "xyz" 1/1/1900
Now I want my ODS history table to be
ODS history:
ID Value Date Flag Changed_Date
1 "abc" 1/1/1900 0 getdate()
2 "efh" 1/1/1900 1 01/12/2015
3 "xyz" 1/1/1900 1 01/12/2015
1 "mno" 1/1/1900 1 01/12/2015
Basically, Flag for old value for ID=1 is 0 and changed_Date= getdate() and new record for ID=1 is inserted in History.
How can i do this? Can you guys please pour in some ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

If you have dates (Changed_Date column) in the history table, then why do you need a flag to tell you whether a row is active or not? You have the real table with the active rows.

You're also making a mistake in saving INSERTs to the history table. That instantly doubles the amount of data you're storing. If you never make an update or a delete to the main table, your history table should be empty because these types of history tables should ONLY record changes and they should be in the form of the old row. The new row will always be available in the real table.

If the whole table is a combination of history and active rows (you have just one table instead of a real table and a history table), then you still don't need the flag because the dates will tell you if a row is active or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 2

Flags are not always selective, which typically makes them a poor indexing choice. You need to understand and follow database normalization basics (at least down to 3NF), before attempting to design tables. After mastering database normalization, consider auditing, change data capture, or create a harness for your own auditing table design (in 3NF). I suggest avoiding triggers. Instead, investigate whether the application can decide what is a change, or not. When there is a change, investigate whether the application can perform the insert into an audit or change table, as needed. Consider the effects of concurrency, and test different transaction isolation levels Appropriate covering indexes will be needed. One so that the application can seek an existing row. Another so that the maximum audit date/attribute can be quickly sought. Post the data definition language (as code) for any table design questions, including all primary, foreign, and candidate keys.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

I'd urge you to use triggers rather than application code for this type of thing. This makes sure the code is consistently applied and prevents having to re-write code to apply the flag logic if multiple sections of code can update the ODS table.

CREATE TRIGGER ODS__TRG_UPDATE

ON dbo.ODS

AFTER UPDATE

AS

SET NOCOUNT ON;

UPDATE oh

SET Flag = 0

FROM deleted d

INNER JOIN ODS_History oh ON oh.ID = d.ID

WHERE

oh.Flag = 1 AND

ISNULL(oh.Value, '~~~') <> ISNULL(d.Value, '~~~') --ISNULLs just handle value being changed to/from NULL to/from non-NULL

GO --end of trigger

Edit: Edited comment to explain use of ISNULL().

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 4

Hi,

Thank you all.

I got the solution from below link

http://mobile.databasejournal.com/features/mssql/managing-slowly-changing-dimension-with-slow-changing-transformation-in-ssis.html

My scenario was SCD type 2 described in the link.