June 8, 2015 at 10:03 am
Hi there!
I am having a problem figuring out the best way to track changes for a very pesky table.
We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.
Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,
[dt_fue] [varchar](25) NULL,
CONSTRAINT [PK_Fake_Name] PRIMARY KEY CLUSTERED
(
[lin] ASC,
[boip_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Anybody help with a clever method to track changes to this table with it being truncated every day?
June 9, 2015 at 5:28 am
you can create one simple audit table for columns you required and insert deleted records into that ....either you write trigger or you can do that by using output deleted.* into tablename.
But for that you need to use delete instead of truncate.
one more way is that before truncate table keep one another master table for the same and insert record into that and it will serve your purpose.
let me know if this could help you or you have any further query.
Raj Acharya
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply