Recreate data as at #date#

Question

0.00/5 (No votes)

See more:

I have a dataset with 50000 rows of data. The data has 112 columns.
I have an update trigger on the data to create Audit Trail when anything changes.
The Audit Table has the following columns:

SQL

CREATE TABLE [dbo].[L_Audit](
	[TableName] [varchar](50) NOT NULL,
	[FieldName] [varchar](50) NOT NULL,
	[ID] [varchar](50) NOT NULL,
	[OldValue] [varchar](max) NULL,
	[NewValue] [varchar](max) NULL,
	[ChangedBy] [varchar](50) NOT NULL,
	[ChangedOn] [varchar](50) NOT NULL)

--TableName - which table triggered the update
--fieldName - which field was updated
--ID - unique identifier of the data that was changed

Questions is, how can I create Point in Time position ?

What I have tried:

Tried to use joins but the problem is I am unable to determine the last updated value i.e. min(ChangedOn) value. The other problem is I do not want to recursively lookup.

Select Top 10 [ID],[Supplier],OldValue,CASE WHEN fieldname='Supplier' THEN OldValue ELSE [Supplier] END as [C_Supplier] from [dbo].BaseData BD
LEFT JOIN L_Audit ON
L_Audit.ID=I.[ID]
where TableName='BaseData'

Posted 4-Apr-19 2:17am

kasbaba

Updated 4-Apr-19 2:48am

Add a Solution

Comments

Richard Deeming 4-Apr-19 9:20am

[ChangedOn] [varchar](50)

If that's meant to be the date and time when the value was changed, then you should be using a proper date+time type - I'd suggest datetime2 or datetimeoffset.

kasbaba 5-Apr-19 1:59am

This is corrected. Thanks for pointing out. Although I was using CAST in the query.

Rob Philpott 4-Apr-19 9:53am

A bit confused. So you have a traditional 'live' table which shows the latest values for these 50,000 records, and you also have a key/value audit table which stores the whole history of the other table? And I presume you want to do a 'point in time' query, where you can see how the table would have looked at that point in the past? And when you add a new row to the main table, does it create 112 rows in the audit table?

kasbaba 5-Apr-19 1:58am

Hi Rob - yes. What i want to accomplish is a query which can show me the Point in Time data using the Audit (Log table) and the base data table.

And no it doesn't add 112 rows. A row is only inserted when something has changed. So we do not have any entries in the Log table for newly created items. Only if they change during their lifecycle would be expect them to see them in the log table.

Hope this makes sense.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-04-04T02:49:00

You haven't stated which version of SQL Server you are using, but from SQL 2016 there a "Temporal tables" designed to recreate the data at a specific time - see SQL 2016 – Temporal Tables – What are they and how do you set one up? – SQL Server Premier Field Engineer Blog[^]

If you are using earlier versions of SQL Server then why not store the last updated datetime on the table itself instead of on a separate audit table?

Alternatively, capture the latest audit entry per item in a temporary table or CTE e.g.(NB untested and may contain typing errors)

SQL

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ChangedOn DESC) AS rn
   FROM L_Audit where TableName='BaseData'
)
select [ID],[Supplier],CTE.OldValue,
CASE WHEN fieldname='Supplier' THEN CTE.OldValue ELSE [Supplier] END as [C_Supplier] from [dbo].BaseData BD
LEFT JOIN CTE ON L_Audit.ID=CTE.[ID]
WHERE rn = 1