title: SQL Server Bulk Insert/Update: checking if record exists with duplicates in source
body: <p>I have the following tables which join on idProduct = id (one to one):</p>
  
  <p>Products</p>
  
  <ul>
  <li>idProduct </li>
  <li>description</li>
  </ul>
  
  <p>ProductKeys</p>
  
  <ul>
  <li>id</li>
  <li>ProductKey</li>
  </ul>
  
  <p>I need to create a trigger on the Products table for single and bulk inserts and updates which updates the key in ProductKeys for a product based on the product description.</p>
  
  <p>If the product has a key, update the key to the description, else insert a new key.</p>
  
  <p><b>The product key has to be unique per product.</b></p>
  
  <p>So, if the key already exists for a different product, append the idProduct to the description to create the key.</p>
  
  <p>My problem:</p>
  
  <p>I have the trigger working for insert and update except for scenarios where there are <b>2 different products being inserted or updated with the same description</b>. In this scenario, the key that's being generated is the same for both products.</p>
  
  <p>Any ideas?</p>
  
  <p>Here's my trigger using a MERGE statement:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[UpdateKey]
  ON  [dbo].[Products] 
  AFTER  INSERT, UPDATE
  AS 
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
          MERGE ProductKeys [Target]
          USING 
              (
                  SELECT
                      CASE 
                          WHEN pk.id IS NULL 
                              THEN i.description
                          ELSE  
                              i.description + '_' + CAST(i.idProduct AS VARCHAR)
                          END [NewProductKey]
                      , i.idProduct
                  FROM
                      INSERTED i
                      LEFT OUTER JOIN ProductKeys pk ON pk.id &lt;&gt; i.idProduct AND pk.ProductKey = i.description
                  ) [source]
          ON (target.id = source.idproduct)
          WHEN NOT MATCHED THEN 
              INSERT
                  ( 
                      id
                      , ProductKey
                  )
              VALUES
                  (
                          source.idProduct
                          , source.NewProductKey
                  )
          WHEN MATCHED THEN
              UPDATE SET
                  ProductKey = source.NewProductKey;
  </code></pre>