T-SQL - Is there a (free) way to compare data in two tables?

Question

I have table a and table b. (SQL Server 2008)

Both tables have the exact same schema.

For the purposes of this question, consider table a = my local dev table, table b = the live table.

I need to create a SQL script (containing UPDATE/DELETE/INSERT statements) that will update table b to be the same as table a. This script will then be deployed to the live database.

Any free tools out there that can do this, or better yet a way I can do it myself?

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.

Anyone have any ideas?

EDIT

Okay, thought I'd clarify this question a little.

The table I need to synchronize is a simple look-up table. The data is very simple and straightforward.

Here's an idea of what TABLE A might look like:

IdFoo          Activity      IsFoo
1              Foo           1
2              Bar           0

Here's an idea of what TABLE B might look like:

IdFoo          Activity      IsFoo
1              Foo           1
2              Bar           1

Basically, all I want to do is update that BIT column (IsFoo) in TABLE B to match the corresponding value in TABLE A for the same IdFoo.

Keep in mind:

• TABLE A is on my local machine
• TABLE B is on the live server

Obviously I have a (reliable) backup of TABLE B on my local machine which i need to script against, then run the script on the live server.

The table also has referential integrity (other columns I didn't show). Which means I can't just delete everything in TABLE B and do an insert from TABLE A.

This script will be a once off. So no need to do things like linked server, replication, etc. Appreciate the answers though guys. =)

EDIT:

Ok - so I've gone with Oleg's answer (VS2010 Data Compare). Quick, easy, and works a charm.

Not to say the other answers are incorrect. I appreciate all the answers!

Answer 1

Late answer but can be useful to the thread visitors

Besides other mentioned solutions, I can suggest trying ApexSQL Data Diff. It can compare and sync SQL Server database data (in live databases as well as backups), and automate and schedule data migrations. It works with huge databases also, and is able to perform comparisons directly from SSMS.

You can download this tool for free and play with it. It features a fully functional free trial, and offers a community edition (after the trial period is over) that works on SQL Express and Azure SQL Database.

To learn more about the tool, you may visit http://www.apexsql.com/sql_tools_datadiff.aspx

Answer 2

In very simple cases you will be able to define in your local SQL Server a new Linked Server (see in Microsoft SQL Server Management Studio under "Server Objects" / "Linked Server") and use INNER JOIN and OUTER JOIN to find out differences between A and B tables.

In a real and more complex situation you should take into consideration Referential Integrity, different Foreign Keys, and Identity (auto incremental) fields existing in the destination database, so the update script will be more complex. So I'll recommend you don't spend your time in creating synchronization of your developer and production DB and use a standard tool instead. I use for example the features of Visual Studio Team Edition 2008 (or Database edition) or Visual Studio 2010 Ultimate edition to compare data in two databases. It works very well.

Answer 3

If you just want to synchronise the tables and don't care about reviewing the changes in advance the MERGE command can do this.

MSDN - MERGE (Transact-SQL)

The (free) Microsoft SSDT also has data compare and synchronization built in though it is more limited than paid tools such as Redgate data compare.