运用Merge Into实现增加或更新数据

  declare @SqlStr as nvarchar(max)
  set @SqlStr=N'Merge Into [Categories] t 
  USING(VALUES (9,''rice'',''main food'',null))
  AS s ([CategoryID],[CategoryName],[Description],[Picture])
  on t.[CategoryID]=s.[CategoryID]
  When Matched Then Update Set t.[CategoryName]=s.[CategoryName],
                               t.[Description]=s.[Description],
                               t.[Picture]=s.[Picture]
  When Not Matched Then Insert ([CategoryName],[Description],[Picture])
                        values(s.[CategoryName],s.[Description],s.[Picture]);'
  exec sp_executesql @SqlStr

View Code

运行截图：