|
|
|
|
|
How can I compare that the
contents of two tables are identical? A couple of methods :- 1. BCP them both out and use the NT COMP.EXE command to compare them. 2. (Courtesy of Roy Harvey) If tableA and tableB have unique indexes on them: select count(*) from tableA select count(*) from tableB If they don't have unique indexes on them, then do select count(*) from (select distinct * from tableA) as a select count(*) from (select distinct * from tableB) as b Check that the counts are the same. If they are not then obviously the tables don't match. If the counts do match then do: select count(*) from (select * from tableA UNION select * from tableB) as t If the count from this query is the same as the previous counts, then the two tables are identical. This is because a UNION does a DISTINCT merge of the two resultsets. Therefore the two tables will "collapse" back into one if and only if they are identical. |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.