begin tran

DECLARE @hotelcd1 char(10) = 'HT04003119' --> Fill in to update

select @hotelcd1, hotelnm from HEM037 where hotelcd = @hotelcd1 

select * from HEM672 where hotelcd = @hotelcd1 and msprogcd = 'MBP1600002'
 

insert into HEM672 (
      msprogcd, hotelcd, stsjoin, joindate, stsearnallroom
      , stsearnalltier, stsearnallifs, stsearnallpkg, stsearnallpromo, stsredeemallroom
      , stsredeemalltier, stsredeemallifs, stsredeemallpkg, stsredeemallpromo, lastupdate
      , updater, stslrg, lrgdiscpct, lrgtermsconditions, stsapprove
      , stsinstredalltier, stsinstredallroom, stsinstredallifs, stsinstredallpkg, stsinstredallpromo
      , msprogtagline, welcomembtag, confirmrsvtag, bepwrbytxt, stsdpmbonlyunrestrict
      , mbbenefitshorttxt, mbbenefitlongtxt)
(
      select
            msprogcd, @hotelcd1, stsjoin, GETDATE(), stsearnallroom
            , stsearnalltier, stsearnallifs, stsearnallpkg, stsearnallpromo, stsredeemallroom
            , stsredeemalltier, stsredeemallifs, stsredeemallpkg, stsredeemallpromo, GETDATE()
            , 'AU00000001', stslrg, lrgdiscpct, lrgtermsconditions, stsapprove
            , stsinstredalltier, stsinstredallroom, stsinstredallifs, stsinstredallpkg, stsinstredallpromo
            , msprogtagline, welcomembtag, confirmrsvtag, bepwrbytxt, stsdpmbonlyunrestrict
            , mbbenefitshorttxt, mbbenefitlongtxt
      from 
            HEM672
      where
            hotelcd='HT03001475' and msprogcd = 'MBP1600002'
)

select * from HEM672 where hotelcd = @hotelcd1 and msprogcd = 'MBP1600002'

rollback