title: Comparing table with sql/tsql in SQL Server 2012
body: <p>I have four tables in SQL Server 2012: <code>table1, table2, table3</code>, and a (resultant/output table) <code>table4</code> </p>
  
  <ol>
  <li><p><code>table1</code> is truncated and loaded with new data daily.</p></li>
  <li><p><code>table1</code> columns <code>col1, col2, col3</code> are similar to <code>table2</code> columns <code>col7, col18, col9</code>, and to <code>table3</code> columns <code>col9, col11, col7</code>, so we can load match rows fro <code>table2</code> and <code>table3</code></p></li>
  </ol>
  
  <p>Problem </p>
  
  <p>Looping table1 to check for every row (col1,col2,col3) matches any row in the table2 (col7,col18,col9) then add row in table4 containing information table1.col1,table1.col2,table1.col3, table2.col6,table3.col1,table3.col7 and a column to indicate its an update</p>
  
  <ul>
  <li><p>if row (col1,col2,col3) in table1 does not exist in table2 add row in table4 containing information table1.col1,table1.col2,table1.col3, table2.col6,table3.col1,table3.col7 and a column to indicate its an addition</p></li>
  <li><p>if row (col7,col18,col9) in table2 does not exist in table1 add row in table4 containing information table1.col1,table1.col2,table1.col3, table2.col6,table3.col1,table3.col7 and a column to indicate its an deletion</p></li>
  </ul>
  
  <p>return table4 as result</p>
  
  <p>How can we do this with SQL/TSQL only?</p>