title: T-SQL - Is there a (free) way to compare data in two tables?
body: <p>I have <code>table a</code> and <code>table b</code>. (SQL Server 2008)</p>
  
  <p>Both tables have the exact same schema.</p>
  
  <p>For the purposes of this question, consider <code>table a</code> = my local dev table, <code>table b</code> = the live table.</p>
  
  <p>I need to create a SQL script (containing <code>UPDATE/DELETE/INSERT</code> statements) that will update table b to be the same as table a. This script will then be deployed to the live database.</p>
  
  <p>Any free tools out there that can do this, or better yet a way I can do it myself?</p>
  
  <p>I'm thinking I probably need to do some type of a join on all the fields in the tables, then generate dynamic SQL based on that.</p>
  
  <p>Anyone have any ideas?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Okay, thought I'd clarify this question a little.</p>
  
  <p>The table I need to synchronize is a simple look-up table. The data is very simple and straightforward.</p>
  
  <p>Here's an idea of what <code>TABLE A</code> might look like:</p>
  
  <pre><code>IdFoo          Activity      IsFoo
  1              Foo           1
  2              Bar           0
  </code></pre>
  
  <p>Here's an idea of what <code>TABLE B</code> might look like:</p>
  
  <pre><code>IdFoo          Activity      IsFoo
  1              Foo           1
  2              Bar           1
  </code></pre>
  
  <p>Basically, all I want to do is update that <code>BIT</code> column (<code>IsFoo</code>) in <code>TABLE B</code> to match the corresponding value in <code>TABLE A</code> for the same IdFoo.</p>
  
  <p>Keep in mind:</p>
  
  <ul>
  <li><code>TABLE A</code> is on my local machine</li>
  <li><code>TABLE B</code> is on the live server</li>
  </ul>
  
  <p>Obviously I have a (reliable) backup of <code>TABLE B</code> on my local machine which i need to script against, then run the script on the live server.</p>
  
  <p>The table also has referential integrity (other columns I didn't show). Which means I can't just delete everything in <code>TABLE B</code> and do an insert from <code>TABLE A</code>.</p>
  
  <p>This script will be a once off. So no need to do things like linked server, replication, etc. Appreciate the answers though guys. =)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Ok - so I've gone with Oleg's answer (VS2010 Data Compare). Quick, easy, and works a charm.</p>
  
  <p>Not to say the other answers are incorrect. I appreciate all the answers!</p>