在更新表数据的情况下，将旧数据与新数据一起作为data_log插入 [英] Insert old data along with new data as data_log in case of update table data

问题描述

我有一个表

TABLE-  EMP_TABLE

EMP_ID                 FIRST_NAME          LAST_NAME              MOBILE_NO
101                     Sunil                Gupta                98989899989

我正在更新表EMP_TABLE通过在桌子下面加入



TABLE-TEMP_DATA

I am Updating table EMP_TABLE BY JOINING Below TABLE

TABLE- TEMP_DATA

EMP_ID            FIRST_NAME            LAST_NAME                MOBILE_NO
101                Sunil                SINGH                   9989887211

UPDATE EMP SET EMP.FIRST_NAME=T.FIRST_NAME,EMP.LAST_NAME=T.LAST_NAME,EMP.MOBILE_NO=T.MOBILE_NO
FROM EMP_TABLE EMP
INNER JOIN TEMP_DATA T ON T.EMP_ID=EMP.EMP_ID

但在更新之前我想在表中记录旧值和新值 -

DATA_LOG

But before update the value I want to log old and new value in table--
DATA_LOG

-----------------------------------------------------------------------------------
ID      EMP_ID       FIELD_NAME        TABLE_NAME      OLD_VALUE         NEW_VALUE
-------------------------------------------------------------------------------------
1        101          FIRST_NAME        EMP_TABLE       Sunil            Sunil
2        101          LAST_NAME         EMP_TABLE       Gupta            Singh
3        101          MOBILE_NO         EMP_TABLE       98989899989      9989887211

以上详细信息仅为示例表DATA_LOG是通用表所有多个数据数据更新过程中的数据存储



请建议在DATA_LOG表中插入数据的最佳方法。



我尝试了什么：



我尝试过使用UNPIVOT但没有得到实际解决方案---

Above Details is just sample table DATA_LOG are generic Table in which all multiple data data store in case of data update process

Please suggest best way to insert data in DATA_LOG table.

What I have tried:

I have tried using UNPIVOT but not get actual solution---

SELECT FieldName,OLDVALUE,NewValue 
			FROM
			(
              select 
              T.FIRST_NAME  'FIRST_NAME',
              T.MIDDLE_NAME  'MIDDLE_NAME',
              T.LAST_NAME  'LAST_NAME',
              T.CARE_OF_NAME 'CARE_OF_NAME',
              T.GENDER 'GENDER',
              T.CONNECTION_HOUSE_NO 'CONNECTION_HOUSE_NO',
              T.CONNECTION_STREET_NO 'CONNECTION_STREET_NO',
              T.CONNECTION_CITY 'CONNECTION_CITY'
              from #TEMPDATA  T
              
   )P
   --INNER JOIN JVVNL_CRM.CRM.CONSUMERS C ON C.K_NO='210111032566'
   UNPIVOT(OLDVALUE,NewValue  FOR FieldName  IN 
				 (p.FIRST_NAME,p.MIDDLE_NAME,p.LAST_NAME,p.CARE_OF_NAME,p.GENDER,p.CONNECTION_HOUSE_NO,p.CONNECTION_STREET_NO,p.CONNECTION_CITY)
			)AS unpvt;

推荐答案

不确定为什么你认为像UNPIVOT这样的随机技术会为你审核你的表。正如@ 011111100010在评论中所述，听起来你需要一个审计触发器 - 这是一个教程，将告诉你如何做到这一点创建一个简单的SQL Server触发器来构建审计跟踪 [ ^ ] br />


你这样做的动机不明确所以也许你也应该熟悉如何读取SQL Server数据库事务日志 [ ^ ]



如果您选择手动执行此操作，则必须执行某些操作e

Not sure why you think some random technique like UNPIVOT would audit your table for you. As @011111100010 stated in the comments, it sounds like you need an audit trigger - here is a tutorial that will show you how to do that Create a Simple SQL Server Trigger to Build an Audit Trail[^]

Your motivation for doing this is not clear so perhaps you should also familiarise yourself with How to read the SQL Server Database Transaction Log[^]

If you choose to do this manually then you would have to do something like this

INSERT INTO DATA_LOG (EMP_ID, FIELD_NAME, TABLE_NAME, OLD_VALUE, NEW_VALUE)
SELECT EMP.EMP_ID, 'FIRST_NAME', 'EMP_TABLE', EMP.FIRST_NAME AS OLD_VALUE, T.FIRST_NAME AS NEW_VALUE

INSERT INTO DATA_LOG (EMP_ID, FIELD_NAME, TABLE_NAME, OLD_VALUE, NEW_VALUE)
SELECT EMP.EMP_ID, 'LAST_NAME', 'EMP_TABLE', EMP.LAST_NAME AS OLD_VALUE, T.LAST_NAME AS NEW_VALUE

INSERT INTO DATA_LOG (EMP_ID, FIELD_NAME, TABLE_NAME, OLD_VALUE, NEW_VALUE)
SELECT EMP.EMP_ID, 'MOBILE_NO', 'EMP_TABLE', EMP.MOBILE_NO AS OLD_VALUE, T.MOBILE_NO AS NEW_VALUE

（假设ID是一个标识列）真的非常糟糕，我能做的最好的论点请改为使用触发器。



顺便说一句，如果你正在进行审计，你不认为同时捕获日期时间是个好主意。变化，可能是谁做到了？如果空间非常宝贵，那么为什么还要在审计表上保留新值 - 这将是当前在EMP_TABLE上的值或存储在此EMP_ID的下一个审计日志中的OLD_VALUE。



最后，我相信TEMP_DATA只包含您肯定要在EMP_TABLE上更改的值，因为您省略了任何WHERE子句。

(assuming the ID is an identity column) Truly awful, and the best argument I could give for using a Trigger instead.

Incidentally, if you are doing an audit don't you think it would be a good idea to also capture the date-time of the change, and possible who did it? If space is at a premium then why bother holding the new value on the audit table - this will be either the value currently on the EMP_TABLE or the OLD_VALUE stored on the next audit log for this EMP_ID.

Finally, I trust that TEMP_DATA only contains values that you definitely want to change on EMP_TABLE as you have omitted any WHERE clause.

如果您使用的是上面的2008 Server，我建议在桌面上启用CDC（更改数据捕获）.Below是链接..



[ ^ ]



我有另一个答案，但它只是出于创意目的......

If your using above 2008 Server , i suggest to enable CDC(change Data capture) on table.Below is the link..

[^]

I have another answer but its just for idea purpose...

CREATE TABLE #EMP_TABLE
(
EMP_ID  INT , FIRST_NAME VARCHAR(50),LAST_NAME VARCHAR(50),MOBILE_NO VARCHAR(50)
)
INSERT INTO #EMP_TABLE 
VALUES (101 ,'Santosh','kumar','8985589046');

CREATE TABLE #TEMP_DATA
(
EMP_ID  INT , FIRST_NAME VARCHAR(50),LAST_NAME VARCHAR(50),MOBILE_NO VARCHAR(50)
)
INSERT INTO #TEMP_DATA 
VALUES(101 ,'Sunil ','SINGH','9989887211');

CREATE TABLE #DATA_LOG  -- table for inserting  new and old records.
(
Id int IDENTITY(1,1),Emp_id INT,Table_name VARCHAR(50),FLd_Old_New  VARCHAR(MAX),Modify_Date Datetime
)


UPDATE EMP SET EMP.FIRST_NAME=T.FIRST_NAME,EMP.MOBILE_NO=T.MOBILE_NO
OUTPUT 
inserted.EMP_ID,N'#EMP_TABLE',
'FIRST_NAME:'+deleted.FIRST_NAME+'->'+Inserted.FIRST_NAME+space(2)+
'MOBILE_NO:'+Deleted.MOBILE_NO+'->'+Inserted.MOBILE_NO ,GETDATE()
INTO  #DATA_LOG
FROM #EMP_TABLE EMP
INNER JOIN #TEMP_DATA T ON T.EMP_ID=EMP.EMP_ID 
                                             
select * from #DATA_LOG
OUTPUT :-
Id | Emp_id | Table_name | FLd_Old_New |	Modify_Date
1    101     #EMP_TABLE	  FIRST_NAME:Santosh->Sunil   MOBILE_NO:8985589046->9989887211	2018-09-12 10:28:36.563

这篇关于在更新表数据的情况下，将旧数据与新数据一起作为data_log插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！