April 15, 2008 at 4:21 pm
What I want to do is to track only records that have changed. I have receipts that change every now and then.
I download a full data set from the source system and insert them into the history table.On a daily basis
I will download the full file from the sourse and insert into the receipts table. I then want to compare
today's data against the history. If they are records have a change in the LastreceiptDate or amt, I want
to select them and insert them in a table.
Initially I wanted to use Binary_checksum(), fater reading a bit about it I kinda felt uneasy. It might return
incorect results. Whats the best way of approaching this?
IF OBJECT_ID('tbReceipts_Hist') IS NOT NULL
DROP TABLE dbo.tbReceipts_Hist
GO
CREATE TABLE dbo.tbReceipts_Hist
(
Loanrefvarchar(15) NULL,
LastReceiptAmountnumeric(13, 2) NULL,
LastreceiptDatedatetime NULL,
BCheckSumvarchar(20),
LoadDatedatetime NULL
)
GO
IF OBJECT_ID('tbReceipts') IS NOT NULL
DROP TABLE tbReceipts
GO
CREATE TABLE dbo.tbReceipts
(
Loanrefvarchar(15) NULL,
LastReceiptAmountnumeric(13, 2) NULL,
LastreceiptDatedatetime NULL,
LoadDatedatetime
)
GO
SET ANSI_PADDING OFF
---Insert rows into tbReceipts
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000550',0.00,'',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000815',-500.00,'2007-12-21 00:00:00.000',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000823',-950.00,'2008-04-15 00:00:00.000',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000831',-950.00,'2008-03-26 00:00:00.000',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000849',-950.00,'2008-03-28 00:00:00.000',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000857',-950.00,'2008-04-15 00:00:00.000',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000865',-300.00,'2008-01-31 00:00:00.000',GETDATE())
INSERT tbReceipts(Loanref,LastReceiptAmount,LastreceiptDate,LoadDate) VALUES('4000000873',-250.00,'2008-02-26 00:00:00.000',GETDATE())
-- Insert Rows into History table
Insert tbReceipts_Hist
Select
Loanref
,LastReceiptAmount
,LastreceiptDate
,Binary_Checksum(Loanref,LastReceiptAmount,LastreceiptDate),LoadDate
From tbReceipts
--Change records in tbReceipts
Update tbReceipts Set LastReceiptAmount = LastReceiptAmount + 10.00,LastreceiptDate = LastreceiptDate + 2
from tbReceipts Where Loanref in ('4000000815','4000000873','4000000857')
--Select Changed records and Insert into Table
April 15, 2008 at 5:01 pm
If you do this with a trigger, the INSERT trigger table will have all the rows that changed in it... automatically...
... then , the trigger could insert into the table you want.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2008 at 8:22 am
Another option is to add a Timestamp (rowversion) column to the table, record that as varbinary in the history table, and compare records on that. Since the timestamp column changes any time the row is updated, this will give you a quick way to narrow down the list of which rows have changed.
The one thing to watch out for is that if there is an update command run on the row, even if it doesn't actually change anything, the timestamp column will still be updated.
The way around that is to narrow down the rows to test by comparing the timestamps, then also comparing other columns to see if there were actual changes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply