title: Better way to INSERT/UPDATE a Customer table from a Key/Value style Property table?
body: <p>I have a customer master table called <code>Customer</code>. Everything in this table comes from a key/value style table called <code>Cust_Property</code>.</p>
  <p>The <code>Cust_Property</code> table has 3 columns:</p>
  <pre><code>CustomerID, Property, Value
  </code></pre>
  <p>The <code>Property</code> column may contain <code>First_Name</code> with a value of <code>John</code>. Sort of like a pre-pivoted table. I need to update the columns in <code>Customer</code> table with the values of the associated <code>Property</code> columns in <code>Cust_Property</code> table.</p>
  <p><strong>Rules</strong></p>
  <ul>
  <li>If there is a new <code>CustomerID</code> in the <code>Cust_Property</code> table, it will need to be added as a new row to the <code>Customer</code> table, as well as all appropriate properties.</li>
  <li>All data in the <code>Customer</code> table will also be in the <code>Cust_Property</code> table. That means that not every record needs to be updated. Only those that have changed or are new.</li>
  <li>Records are only added/updated in <code>Customer</code> table, not removed.</li>
  <li>There are properties in the <code>Property</code> table where a corresponding column doesn't exist in the <code>Customer</code> table, so those are just ignored.</li>
  </ul>
  <p><strong>DDL</strong></p>
  <pre><code>CREATE TABLE #Customer
  (
      Customerid int,
      FirstName varchar(50),
      LastName varchar(50),
      Address1 varchar(100),
      Address2 varchar(100),
      Address3 varchar(100)
  )
  
  CREATE TABLE #Cust_Property
  (
      CustomerID int,
      Property varchar(50),
      Value varchar(50)
  )
  
  INSERT INTO #Customer (Customerid, FirstName, LastName, Address1, Address2, Address3) 
  VALUES(1, N'John', N'Smith', N'123 happy lane', NULL, NULL);
  
  INSERT INTO #Customer (Customerid, FirstName, LastName, Address1, Address2, Address3)
  VALUES(2, N'Dwight', N'Schrute', N'33 1st Ave', N'Apt 5', NULL);
  
  INSERT INTO #Customer (Customerid, FirstName, LastName, Address1, Address2, Address3)
  VALUES(3, NULL, NULL, NULL, NULL, NULL);
  
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(3, N'First_Name', N'Michael');
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(3, N'Last_Name', N'Scott');
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(8, N'First_Name', N'Jim');
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(8, N'Last_Name', N'Halpert');
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(8, N'Address1', N'644 Scranton Rd');
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(8, N'Nickname', N'Jimmy');
  
  INSERT INTO #Cust_Property (CustomerID, Property, Value)
  VALUES(1, N'First_Name', N'John');
  </code></pre>
  <p><strong>Tables:</strong></p>
  <p><code>Customer</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerID</th>
  <th>FirstName</th>
  <th>LastName</th>
  <th>Address1</th>
  <th>Address 2</th>
  <th>Address 3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>John</td>
  <td>Smith</td>
  <td>123 happy lane</td>
  <td></td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td>Dwight</td>
  <td>Schrute</td>
  <td>33 1st Ave</td>
  <td>Apt 5</td>
  <td></td>
  </tr>
  <tr>
  <td>3</td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><code>Cust_Property</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerID</th>
  <th>FirstName</th>
  <th>LastName</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>3</td>
  <td>First_Name</td>
  <td>Michael</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Last_Name</td>
  <td>Scott</td>
  </tr>
  <tr>
  <td>8</td>
  <td>First_Name</td>
  <td>Jim</td>
  </tr>
  <tr>
  <td>8</td>
  <td>Last_Name</td>
  <td>Halpert</td>
  </tr>
  <tr>
  <td>8</td>
  <td>Address1</td>
  <td>644 Scranton Rd</td>
  </tr>
  <tr>
  <td>8</td>
  <td>Nickname</td>
  <td>Jimmy</td>
  </tr>
  <tr>
  <td>1</td>
  <td>First_Name</td>
  <td>John</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Desired <code>Customer</code> table end result:</p>
  <ul>
  <li><code>Customer</code> 3's <code>First_Name</code> and <code>Last_Name</code> columns are updated</li>
  <li>Add customer 8 to <code>Customer</code> table b/c it doesn't already exist</li>
  <li>Update all of customer 8's properties except property <code>Nickname</code> because that doesn't exist in the <code>Customer</code> table</li>
  <li>Ignore the <code>First_Name</code> property for <code>CustomerID = 1</code> because it is the same in the <code>Customer</code> table, so no update needed.</li>
  </ul>
  <p>My current method: first find and insert new <code>CustomerID</code>s</p>
  <pre><code>INSERT INTO #Customer (CustomerID)
      SELECT DISTINCT CustomerID 
      FROM #Cust_Property a
      WHERE NOT EXISTS (SELECT * FROM #Customer x 
                        WHERE a.CustomerID = x.CustomerID)
  </code></pre>
  <p>Then update properties</p>
  <pre><code> UPDATE #Customer
   SET #Customer.FirstName = a.Value
   FROM #Cust_Property a
   WHERE #Customer.CustomerID = a.CustomerID 
     AND a.Property = 'First_Name'
  
   UPDATE #Customer
   SET #Customer.LastName = a.Value
   FROM #Cust_Property a
   WHERE #Customer.CustomerID = a.CustomerID 
     AND a.Property = 'Last_Name'
  
   UPDATE #Customer
   SET #Customer.Address1 = a.Value
   FROM #Cust_Property a
   WHERE #Customer.CustomerID = a.CustomerID 
     AND a.Property = 'Address1'
      
   UPDATE #Customer
   SET #Customer.Address2 = a.Value
   FROM #Cust_Property a
   WHERE #Customer.CustomerID = a.CustomerID 
     AND a.Property = 'Address2'
      
   UPDATE #Customer
   SET #Customer.Address3 = a.Value
   FROM #Cust_Property a
   WHERE #Customer.CustomerID = a.CustomerID 
     AND a.Property = 'Address3'
  </code></pre>
  <p>In my actual tables, there are hundreds of different properties in <code>Cust_Properties</code> table, 40 columns in the <code>Customer</code> table that need updating and ~2M customer records.</p>
  <p>Is there a better way that separate update statements for each? At present, I can't use an ETL tool, though I could technically use Python if that would be more efficient/faster.</p>