T-SQL - Is there a (free) way to compare data in t

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

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.

Answer 2

Tablediff would work - it's free and comes with SQL Server http://msdn.microsoft.com/en-us/library/ms162843.aspx

Answer 3

You can also have a look at xSQL Data Compare. The SQL Express version is free and there is also a Lite version that will do the trick for small databases.

A good free tool is also data compare from SSDT.

Disclaimer: I'm affiliated with xSQL.

Answer 4

I ran in the same problem as you - searched for a free tool that compares data from two MS SQL tables - and found nothing. Then I created a simple freeware command line utility. It compares data from two tables, and creates INSERT/DELETE/UPDATE statemenets to make destination table data the same as source. Now I am using it for data comparison, and as it is completely free, can recommend to check it out at: Sourceforge.net - UltraDBC

Answer 5

There is SQL Data Compare from RedGate (though not free) and there is also SMO and the built in functionallity.

Finally Wikipedia has quite a comprehensive list of software.

Answer 6

You might try our Schema Compare for SQL Server

This tool is not free (it's shareware), but you can use 30-days trial for free and also, you have an opportunity to get a free license for this product - please refer to our free license conditions page.