如何使用MERGE或Upsert Sql语句 [英] How to use MERGE or Upsert Sql statement
问题描述
如何为下面的代码使用MERGE
Sql语句或UPDATE
语句.我有一个名为MachineName
的columnName,其他列值发生了变化,但是MachineName却没有变化.如果列MachineName
更改,则需要在第二行中插入新值.如果不是,我需要更新同一行.我怎样才能做到这一点.这是正确的方法吗?请帮助
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);
推荐答案
您可以将新的Machine数据加载到Temporary表中,然后可以使用Merge语句来更新Machine中已经有记录的记录表,如果机器表中不存在该记录,则会插入一条新记录.
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);
这篇关于如何使用MERGE或Upsert Sql语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!