Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a Table
TABLE-  EMP_TABLE

EMP_ID                 FIRST_NAME          LAST_NAME              MOBILE_NO
101                     Sunil                Gupta                98989899989
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

SQL
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
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
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---
SQL
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;
Posted
Updated 11-Sep-18 19:22pm
v4
Comments
ZurdoDev 10-Sep-18 14:11pm    
Sounds like you want an audit trigger.
suneel kumar gupta 11-Sep-18 0:18am    
Data_Logs table are common table in which if we want to updated a table's data if required then store old values in data log table
Santosh kumar Pithani 11-Sep-18 23:40pm    
your expecting new and old records from this update query only or on table ?USE CDC on table so you can find all DML records in table.

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
SQL
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
(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.
 
Share this answer
 
Comments
suneel kumar gupta 11-Sep-18 4:36am    
I agree with you but Data_Log contains data from multiple source table not for for EMP Table,

And the manual logic to insert data for each filed is not possible because sometimes more than 10 fields are updating in table so it will be lengthy process..
CHill60 11-Sep-18 6:10am    
I'm not suggesting that you use the manual process - I only showed it to demonstrate how painful it would be to do it that way... quote: "Truly awful, and the best argument I could give for using a Trigger instead."
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...
SQL
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900