How to use MERGE or Upsert Sql statement

Question

How can i use MERGE Sql Statement or UPDATE statement for my below code. I am having a columnName called MachineName, other column values change but MachineName doesnot change. If the Column MachineName changes i need to insert the new values in a secondrow. If not i need to Update the same row. How can i do this. Is it a right approach ? Please help

MERGE INTO [devLaserViso].[dbo].[Machine] WITH (HOLDLOCK) USING [devLaserViso].[dbo].[Machine] ON (MachineName = MachineName) WHEN MATCHED THEN UPDATE SET MachineName = L1,ProgramName= ancdh.pgm, TotalCount= 10, RightCount=4, LeftCount= 3,ErrorCode=0,FinishingTime=fsefsefef WHEN NOT MATCHED THEN INSERT (MachineName, ProgramName, TotalCount, RightCount, LeftCount, ErrorCode, FinishingTime) VALUES (L02, djiwdn.pgm, 11, 5, 4, 0, dnwdnwoin);

Sample data and desired results would be very enlightening. You are doing a self join, and the logic doesn't make sense. — Gordon Linoff
– Gordon Linoff, Commented May 8, 2018 at 12:02

Ben Koehler · Accepted Answer · 2019-01-10 15:45:54Z

I've had success in the past "Selecting" the values to upsert into the USING section of the MERGE command:

MERGE INTO [devLaserViso].[dbo].[Machine] WITH (HOLDLOCK) AS Target USING (SELECT 'L1' AS MachineName, 'ancdh.pgm' AS ProgramName, 10 AS TotalCount, 4 AS RightCount, 3 AS LeftCount, 0 AS ErrorCode, 'fsefsefef' AS FinishingTime) AS Source ON (Target.MachineName = Source.MachineName) WHEN MATCHED THEN UPDATE SET ProgramName= Source.ProgramName, TotalCount= Source.TotalCount, RightCount= Source.RightCount, LeftCount= Source.LeftCount, ErrorCode= Source.ErrorCode, FinishingTime= Source.FinishingTime WHEN NOT MATCHED THEN INSERT (MachineName, ProgramName, TotalCount, RightCount, LeftCount, ErrorCode, FinishingTime) VALUES (Source.MachineName, Source.ProgramName, Source.TotalCount, Source.RightCount, Source.LeftCount, Source.ErrorCode, Source.FinishingTime);

Alankrit Agrawal · Accepted Answer · 2018-05-08 11:48:28Z

You can load the new Machine data in a Temporary table and then can use the Merge statement as follows to update the records for which there is already a record in Machine table and will insert a new record if it does not exists in Machine table.

MERGE [devLaserViso].[dbo].[Machine] t WITH (HOLDLOCK) USING [devLaserViso].[dbo].[TempMachine] s ON (s.MachineName = t.MachineName) WHEN MATCHED THEN UPDATE SET t.MachineName = s.MachineName,t.ProgramName =s.ProgramName WHEN NOT MATCHED BY TARGET THEN INSERT (MachineName,ProgramName) VALUES (s.MachineName, s.ProgramName);

Collectives™ on Stack Overflow

How to use MERGE or Upsert Sql statement

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related