SQLServer Merge

只需要一次扫描

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								  );