title: T-SQL Conversion error in T-SQL Merge statement
body: <p>I am trying to build an optimized Slowly Changing Dimension using the Merge statement in T-Sql. I have written the following code to handle SCD1 and SCD2 changes, and also normal inserts in the data table, with data coming from the source table, Name and Age are SCD1 columns, Animal and Blood are SCD2 columns:</p>
  <pre><code>DECLARE @LoadingDate DATETIME
  SET @LoadingDate = '2012-08-20 14:23:29.827'
  
  --Handle SCD1 Changes
  MERGE INTO Table_2 AS DIM
   USING SourceTable AS SRC
   ON (DIM.ID1 = SRC.ID1
      AND DIM.ID2 = SRC.ID2)
   WHEN MATCHED
      AND (DIM.Name &lt;&gt; SRC.Name 
        OR DIM.AGE &lt;&gt; SRC.AGE)
      THEN 
      UPDATE 
      SET DIM.Name = SRC.Name,
      DIM.Age = SRC.Age;
  
  --Handle SCD2 Changes
  INSERT INTO Table_2
      (ID1, ID2, --Business Key
       Name, Age, --SCD1 Columns
       Animal, Blood, --SCD2 Columns
       DateEffective, DateExpires)
   SELECT
      ID1, ID2, --Business Key
       Name, Age, --SCD1 Columns
       Animal, Blood, --SCD2 Columns
       DateEffective, DateExpires
  FROM (
  MERGE Table_2 AS DIM
   USING SourceTable AS SRC
   ON (DIM.ID1 = SRC.ID1
      AND DIM.ID2 = SRC.ID2)
   WHEN NOT MATCHED
    THEN INSERT VALUES 
    (SRC.ID1, SRC.ID2,
     SRC.Name, SRC.Age,
     SRC.Animal, SRC.Blood,
     @LoadingDate, NULL)
   WHEN MATCHED 
     AND DIM.DateExpires IS NULL
     AND (DIM.Animal != SRC.Animal
      OR DIM.Blood != SRC.Blood)
     THEN UPDATE SET DIM.DateExpires = @LoadingDate
   OUTPUT $action Action_Out,
     SRC.ID1, SRC.ID2,
     SRC.Name, SRC.Age,
     SRC.Animal, SRC.Blood,
     @LoadingDate AS DateEffective,
     NULL AS DateExpires) AS MERGE_OUT
   WHERE MERGE_OUT.Action_Out = 'UPDATE';
  </code></pre>
  <p>The code works fine with SCD1 changes(first part of the code), but it gives me the error:
  <img src="https://i.stack.imgur.com/pCG4A.jpg" alt="enter image description here" />
  The error appears when it tries to insert a new row, for which the business keys ID1 and ID2 don't match with any other row from the Data Table, and &quot;Labus&quot; is the value in the name field.</p>
  <p>Both tables are designed like in the following picture, but the SourceTable doesn't have housekeeping columns:
  <img src="https://i.stack.imgur.com/txMh0.jpg" alt="enter image description here" /></p>
  <p>I would really appreaciate some help.
  Thanks!</p>