Currently I use RedGate’s SQL Data Compare to see differences in data across databases. It’s a great product. One thing I love about RedGate is the usability of their products. All of them are intuitive and easy to use. What if you don’t have the cash to do this though?
You could use the EXCEPT (introduced in SQL Server 2005) clause to do your comparison. For example:
SELECT * FROM #table2 -- returns 30 records EXCEPT SELECT * FROM #table1 -- returns 10 records.
You get a result set of 20 records if those 10 records in the table1 statement are in table2. You can think of it as subtracting records from table2 that are in table1. You will get the end result of 20 records.
The following statement builds on that taking the end result and putting it back into table1. This is useful if you want to sync an outdated table (in this case table1) with another table that has additional records (table2).
INSERT INTO #table1 SELECT * FROM #table2 -- latest data table (we'll be copying from here) EXCEPT SELECT * FROM #table1 -- outdated table (has old data)
Other tools to compare data across tables: CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. The great thing about this tool is also that it will check results of two SQL statements / stored procs. Useful when unit testing.
Also you can use TableDiff.exe in your “C:\Program Files\Microsoft SQL Server\90\COM” directory. Learn more about it at the SQL team website.