title: Compare two tables across databases
body: <p>I have a table with two columns...... an int ID and a varchar userName. We want to count the number of records from one table and whether they exist in the other table.</p>
  
  <p>Table 1 is source
  Table 2 is dest</p>
  
  <pre><code>--Clear the table
  DELETE FROM dest
  
  --Allow identity insert
  SET IDENTITY_INSERT dest ON
  
  --copy the source to the destination
  insert into dest(id, userName) select id, userName from source
  </code></pre>
  
  <p>So far, so good.  The problem that i have now, is that some records have now been deleted and we wish to find the differences between source and dest. So imagine some activity has occurred on dest causing it to lose some records that remain existing in source.</p>
  
  <pre><code>--Count number of records in source.   The result here is 18247
  SELECT COUNT(*) FROM source
  
  --Count number of records in dest.  The result here is 18298
  SELECT COUNT(*) FROM dest
  
  --There is a difference of 51 records. Correct??
  
  --Now count all records that exist in source, but not in dest. i.e. records deleted from dest.
  
  SELECT COUNT(*) FROM source AS sourceTable WHERE NOT EXISTS(SELECT 1 FROM dest AS destTable WHERE destTable.id=sourceTable.id)
  
  --The result here is 18165 (???)
  </code></pre>
  
  <p>Clearly the where not exists query is incorrect. Any clues as to what i am doing wrong here? I just want a list of IDs belonging to records that exist in the source, but now no longer exist in dest.</p>