Column names for each of Merge update Statement

Question

Post reply

Column names for each of Merge update Statement

LearnSQL

SSC-Addicted

Points: 407

July 21, 2022 at 9:01 am

#4066358

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 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
October 5, 2024 at 4:30 am

This was removed by the editor as SPAM
October 5, 2024 at 4:35 am

This was removed by the editor as SPAM
October 5, 2024 at 4:39 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24401 More actions · Answer 1

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.

How to post data/code on a forum to get the best help.

LearnSQL SSC-Addicted Points: 407 More actions · Answer 2

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.

DesNorton SSC-Insane Points: 24401 More actions · Answer 3

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

OUTPUT Clause

MERGE

Stairway to T-SQL DML Level 12: Using the MERGE Statement

How to post data/code on a forum to get the best help.

Geneharberl Newbie Points: 9 More actions · Answer 4

This is exactly what I was looking for. thank you.

Papa's freezeria

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

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.

https://sqlblog.org/merge