title: Update, Insert and Delete rows in a table based on changes in another table
body: <p>I have Table A  </p>
  
  <pre><code>ID  |  Address         | Field1 | Field2 | etc  
  1   | 1 Smith St       | a      |  sd    | sdf
  2   | 2 Smith St       | b      |  sf    | ff
  </code></pre>
  
  <p>I have created another table Table B with just the ID and Address and 2 extra fields for Latitude and Longitude which I populate with a separate script</p>
  
  <pre><code>ID  | Address          | Lat    | Long
  1   | 1 Smith St       | 20.23  | 110.34
  2   | 2 Smith St       | 18.34  | 112.43
  </code></pre>
  
  <p>There are 2 things I would like to do. </p>
  
  <ol>
  <li>Check for new records in table A based on the ID field and insert ID, Address into table B as a new row (important)</li>
  <li>Check for changes to the Address field for records in Table A which are already in Table B and update them and clear the lat/long values in table B if the address has changed (not as important but would be nice)</li>
  </ol>
  
  <p>Table A gets updated with a Drop/Create so I don't need to listen for changes I'm just looking for an update/insert query which could do one or both of these. Can be 2 separate queries as well.  </p>
  
  <p><strong>Update:</strong>
  I've manged to solve the first part based on the following post, <a href="https://stackoverflow.com/questions/20778002/sql-insert-rows-from-another-table-based-on-condition">sql insert rows from another table based on condition</a> sorry I thought I had searched enough for an answer. Still unsure on how to go about the 2nd part. Using SQL Server 2014 to address the comments. The following takes care of the first part for me. How can I update the rows in TableB where TableB.ID = TableA.ID but the Address fields are different? </p>
  
  <pre><code>INSERT INTO TableB (ID, ADDRESS)
  SELECT ID, ADDRESS
    FROM TableA t
   WHERE NOT EXISTS
  (
    SELECT * 
      FROM TableA
     WHERE ID = t.ID
  )
  </code></pre>