在＃date＃重新创建数据 [英] Recreate data as at #date#

问题描述

我有一个包含50000行数据的数据集。数据有112列。

我有数据的更新触发器，可以在任何更改时创建审计跟踪。

审计表包含以下列：

  CREATE   TABLE  [dbo]。[L_Audit]（
 [TableName] [ varchar ]（ 50 ）< span class =code-keyword> NOT   NULL ，
 [FieldName] [ varchar ]（ 50 ） NOT   NULL ，
 [ID] [ varchar ]（ 50 ）不  NULL ，
 [OldValue] [ varchar ]（最大值） NULL ，
 [NewValue] [ varchar ]（ma x） NULL ，
 [ChangedBy] [ varchar ]（ 50 ） NOT   NULL ，
 [ChangedOn] [ varchar ]（ 50 ） NOT   NULL ）
 
   -    TableName  - 哪个表触发更新 
   -    fieldName  - 哪个字段已更新 
   -    ID  - 已更改数据的唯一标识符 

问题是，如何创建Point in Time位置？



< b>我尝试了什么：



试图使用连接但问题是我无法确定最后一个更新值即min（ChangedOn）值。另一个问题是我不想递归查找。

选择前10名[ID]，[供应商]，OldValue，CASE WHEN fieldname ='Supplier'那么OldValue ELSE [供应商]从[dbo]结束[C_Supplier] .BaseData BD 
 LEFT JOIN L_Audit ON 
 L_Audit.ID = I。[ID] 
 where TableName ='BaseData'

解决方案

您还没有说明您使用的是哪个版本的SQL Server，但是从SQL 2016那里有一个时间表旨在在特定时间重新创建数据 - 请参阅 SQL 2016 - 时间表 - 它们是什么以及如何设置它？ - SQL Server Premier现场工程师博客 [ ^ ]



如果您使用的是早期版本的SQL Server，那么为什么不将最后更新的日期时间存储在表本身而不是单独的审计表上？



或者，在临时表或CTE中捕获每个项目的最新审计条目，例如（NB未经测试，可能包含输入错误）

;  WITH  cte  AS  
（
  SELECT  *，
 ROW_NUMBER（） OVER （ PARTITION   BY  ID  ORDER   BY  ChangedOn  DESC ） AS  rn 
  FROM  L_Audit  where  TableName = '  BaseData' 
）
 选择 [ID]，[供应商]，CTE.OldValue，
  CASE   WHEN  fieldname = ' 供应商' 那么 CTE.OldValue  ELSE  [供应商]  END   as  [C_Supplier] 来自 [dbo] .BaseData BD 
  LEFT   JOIN  CTE  ON  L_Audit.ID = CTE。[ID] 
  WHERE  rn =  1  

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:

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'

解决方案

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)

;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

这篇关于在＃date＃重新创建数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！