I work predominantly in the data integration space and I suspect most of you reading this do too. One of the most common data integration scenarios is discovering what records in one table don't exist in another and I have previously written about how to achieve that in my tool of choice, SQL Server Integration Services.
Recently another tool provided with SQL Server, primarily for replication scenarios, was brought to my attention. Its called TableDiff.exe and is a command-line tool that quite simply shows you the differences between two tables.
By default TableDiff.exe is stored at %ProgramFiles%\Microsoft SQL Server\90\COM. What I really like about it is that it has the ability to generate SQL statements that when executed will ensure two tables with matching metadata contain the same data. So, if you run this comparison between a populated table and an empty table it will generate all the insert statements that you need to recreate the populated table. That is functionality that is sorely missing from any other tool provided with SQL Server and which, up to now, I have always achieved using sp_generate_inserts.
Read more about TableDiff here.