SQLServer Merge

最新推荐文章于 2022-03-25 11:11:14 发布

a237428367

最新推荐文章于 2022-03-25 11:11:14 发布

阅读量704

点赞数

分类专栏： MSSQL 文章标签： SQLServer Merge

本文链接：https://blog.csdn.net/a237428367/article/details/51607526

版权

MSSQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

只需要一次扫描

MERGE dbo.MATCHESODDS3W AS target
USING(SELECT @MatchID,@Item3wID) AS source(MatchID,Item3wID)
ON (target.MatchID=source.MatchID AND target.Item3wID=source.Item3wID)
WHEN MATCHED THEN
 UPDATE SET
 Item3wActive=@Item3wActive,
 Item3wCombination=@Item3wCombination,
 Item3wSpecialValue=@Item3wSpecialValue,
 Item3w1Active=@Item3w1Active,
 Item3wXActive=@Item3wXActive,
 Item3w2Active=@Item3w2Active,
 UpdateTS=@Now
WHEN NOT MATCHED THEN
 INSERT
 (
  MatchID ,
  Item3wID ,
  Item3wActive ,
  Item3wCombination ,
  Item3wSpecialValue ,
  Item3w1Active ,
  Item3w1Value ,
  Item3w1Outcome ,
  Item3w1Voidfactor ,
  Item3wXActive ,
  Item3wXValue ,
  Item3wXOutcome ,
  Item3wXVoidfactor ,
  Item3w2Active ,
  Item3w2Value ,
  Item3w2Outcome ,
  Item3w2Voidfactor ,
  Clear,
  ClearTS,
  Cancel,
  CancelTS,
  CreateTS ,
  UpdateTS			
 )	
VALUES
 (
  @MatchID , -- MatchID - bigint
  @Item3wID , -- Item3wID - bigint
  @Item3wActive , -- Item3wActive - bit
  @Item3wCombination , -- Item3wCombination - tinyint
  @Item3wSpecialValue , -- Item3wSpecialValue - varchar(10)
  @Item3w1Active , -- Item3w1Active - bit
  @Item3w1Value , -- Item3w1Value - decimal
  0 , -- Item3w1Outcome - bit
  -9 , -- Item3w1Voidfactor - decimal
  @Item3wXActive , -- Item3wXActive - bit
  @Item3wXValue , -- Item3wXValue - decimal
  0 , -- Item3wXOutcome - bit
  -9 , -- Item3wXVoidfactor - decimal
  @Item3w2Active , -- Item3w2Active - bit
  @Item3w2Value , -- Item3w2Value - decimal
  0 , -- Item3w2Outcome - bit
  -9 , -- Item3w2Voidfactor - decimal
  0,
  @DefauktDate,
  0,
  @DefauktDate,
  @Now , -- CreateTS - datetime
  @Now  -- UpdateTS - datetime								  );