title: How can i convert Insert from select to upsert
body: <p>How can i convert insert from select to upsert. I have the following code to insert data from one database table to another, i am able to insert to but if something changes any of the fields which are not part of the PK. the primary key for the <code>WITESTCO.dbo.[WIBOMD]</code> are <code>[bomItem], [bomRev], [bomEntry]</code>. </p>
  
  <pre><code>INSERT INTO WITESTCO.dbo.[WIBOMD] 
     ([bomItem], [bomRev], [bomEntry], [partId], [qty],[cmnt],[srcLoc],[dType],[lead],[lineNbr])
     select 
      [STOCK NO]    
    , u.rev
    , bomEntry = row_number() over (order by u.ordinal)
    , u.Partid
    , Qty='1'
    , cmnt = 'TEST'
    , srcLoc = 'TEST'
    , dType = '0'
    , lead = '0'
    , lineNbr = row_number() over (order by u.ordinal)
  from [inserted]
    cross apply (values 
      ([bomRev],1,[BOM-WHEEL PN])
      ,([bomRev],2,[BOM - RIM])
      ,([bomRev],3,[BOM - SECONDARY DISC PN])
      ,([bomRev],4,[BOM - FIN DISC PN])
      ,([bomRev],5, [BOM - FLAT FIN DISC PN])
      ,([bomRev],6,[WHL BOM PART 1 PN])
      ,([bomRev],7,[WHL BOM PART 2 PN])
      ,([bomRev],8,[WHL BOM PART 3 PN])
      ,([bomRev],9,[WHL BOM PART 4 PN])
      ,([bomRev],10,[WHL BOM PART 5 PN])
      ,([bomRev],11,[COLOR-PN])
  
  ) u (rev, ordinal, partId)
  where nullif(u.partId,'') is not null AND  NOT EXISTS(SELECT * FROM  WITESTCO.dbo.[WIBOMD] WHERE WITESTCO.dbo.[WIBOMD].[bomItem]=inserted.[STOCK NO] and WITESTCO.dbo.[WIBOMD].[bomRev]=inserted.[RevControl]);
  </code></pre>