July 21, 2022 at 9:01 am
Hi All,
I have around 120 columns and it has around 1 million record. Use to receive a csv file and which has new records and as well as old records with the changes and have one PYKey. Based on the Have used Merge statement to update(old record) and insert (new record), how to find which column got updated values for existing record?
Eg:
Existing record
Emp, dept ,deptname,'Address',.............
1 ,1,'HR','Addressone'
2 ,1,'HR','Addresstwo'
Have received my csv file with updated value
Emp, dept ,deptname,'Address',.............
1 ,1,'hr','Address-ONES'
2 ,2,'Admin','Address-TWO'
How to capture the difference that there was an change to each row.
I need to know particular column names alone for that PYkey value.
emp 1 Address
emp 2 Deptname, Address.
July 21, 2022 at 3:55 pm
Doing this with a simple UPDATE, you can construct an output as follows
CREATE TABLE #Employee (
Emp int NOT NULL PRIMARY KEY CLUSTERED
, dept int NULL
, deptname varchar(20) NULL
, [address] varchar(20) NULL
);
INSERT INTO #Employee ( Emp, dept, deptname, [address] )
VALUES ( 1 ,1, 'HR', 'Addressone' )
, ( 2 ,1, 'HR', 'Addresstwo' );
WITH cteNewdata ( Emp, dept, deptname, [address] ) AS (
SELECT src.*
FROM ( VALUES
( 1, 1, 'hr', 'Address-ONES' )
, ( 2, 2, 'Admin', 'Address-TWO' )
) AS src( Emp, dept, deptname, [address] )
)
UPDATE e
SET e.dept = nd.dept
, e.deptname = nd.deptname
, e.[address] = nd.[address]
OUTPUT INSERTED.Emp
, '<Employee Emp="' + CONVERT(varchar(10), INSERTED.Emp)
+ CASE WHEN INSERTED.dept = DELETED.dept THEN '' ELSE '" oldDept="' + CONVERT(varchar(10), DELETED.dept) + '" newDept="' + CONVERT(varchar(10), INSERTED.dept) + '"' END
+ CASE WHEN INSERTED.deptname = DELETED.deptname THEN '' ELSE '" oldDeptName="' + DELETED.deptname + '" newDeptName="' + INSERTED.deptname + '"' END
+ CASE WHEN INSERTED.[address] = DELETED.[address] THEN '' ELSE '" oldAddress="' + DELETED.[address] + '" newAddress="' + INSERTED.[address] + '"' END
+ ' />' AS changeXml
, STUFF(''
+ CASE WHEN INSERTED.dept = DELETED.dept THEN '' ELSE ', dept' END
+ CASE WHEN INSERTED.deptname = DELETED.deptname THEN '' ELSE ', deptname' END
+ CASE WHEN INSERTED.[address] = DELETED.[address] THEN '' ELSE ', address' END
, 1, 1, '') AS changelist
FROM #Employee AS e
INNER JOIN cteNewdata AS nd ON e.Emp = nd.Emp
DROP TABLE #Employee;
You can adapt that to the output of a MERGE statement if needed.
July 22, 2022 at 5:09 am
Thanks.
But I am using Merge statement as there are new records and modified records in the same file, so I need to insert or update accordingly, is there any alternate approach.
July 22, 2022 at 2:28 pm
I gave you an example of how to use the OUTPUT clause, leaving you to modify it to work with your MERGE statement.
These links should help you further
Stairway to T-SQL DML Level 12: Using the MERGE Statement
July 28, 2022 at 11:29 am
This was removed by the editor as SPAM
July 30, 2022 at 8:35 am
This was removed by the editor as SPAM
August 1, 2022 at 4:29 am
This was removed by the editor as SPAM
August 2, 2022 at 11:15 am
This was removed by the editor as SPAM
December 23, 2022 at 2:18 am
December 23, 2022 at 9:17 am
Whenever anyone talks about merge, I always send them to this link first and if they still want to use it, then OK
Merge has a number of issues which are down as wont fix, so it is much better to use an upsert method than to use merge.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply