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?


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. =)


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!

+2  A: 

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.

The RedGate SQL Data Compare tool isn't free. SMO is a good idea though, and the Wikipedia link is a great source.
Bernhard Hofmann
Sorry Bernard I misread part of the question and somehow interpreted it as if free was preferred though optional, I've edited the answer to reflect that. Need more coffee I guess.
+1  A: 

You could use a data script generator that creates script for the inserts, and then use a file comparison tools such as WinMerge to compare the files to find the differences. There's an article on generating the data scripts on code project:

Bernhard Hofmann
+2  A: 

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

Martin Smith

You can also try using the import and export data provided by SQL Server 2008. Its is fairly straight way to copy all the data from anywhere to anywhere. I do the same thing and works perfectly.

+1  A: 

In a 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 befween A and B tables.

In a real more complex situation you should take in consideration Referantial Integrity, different Forein Keys and Identity (autoincrenmental) firlds existing in the destintion database, so the update script will be more complex. So I'll recomend you don't spend your time in creating syncronisation of your developer and production DB and use a standard tool instead. I use for example the features of Vinsual Studio Team Edition 2008 (or Database edition) or Visual Studio 2010 Ultimate edition to compare data in two databases. It works very good.

Ive got VS2010 - how do you compare data in two databases with that? Do you have a reference/link?
Just go in menu "Data" choose "Data Compare" and "New Data Comparison". Then choose source and destination databases and "Data Compare Options". Then follow the wizard. At the end you can examine comparing results, "destelect" some differences, and either write the reschanges directly in the destination database or generate a SQL Script. In the "Tool" "Options" menu you can chose some code generation options. You can not only compare data, but also compare schema of databases if needed.
Cool - ok ill give that a go tomorrow (when im back at work) and let u know how i go - cheers for the tip!
You can read more about Compare and Synchronize Data in Visual Studio 2010 under By the way if needed, you can automate Data Compare through DTE (see
That's awesome! And works beautifully. Cheers!

Since it is one off, you can use this query to find rows that are different in this two tables:




MINUS will compare records field by field, then it will discard records from first table for which there is identical record in second table. This works like this:

  • First MINUS gets all records from TABLE_A that are not in TABLE_B
  • Second MINUS gets all records from TABLE_B that are not in TABLE_A
  • Union gets all records from both tables for which there is no matching record in other table.

Now you can insert those records in some temp table and then do inserts/updates.

Depending on your needs, you can restrict field list for comparing.

Note that you need primary key for this to work.

Ooops. SQL Server does not support MINUS operator. I'm working with ORACLE last year and half, so this was automatic.

You can use EXCEPT operator instead. See this article: EXCEPT and INTERSECT (Transact-SQL)

Edit 2:

Re scherand's comment:
If he really cannot connect from local machine to live server, than he can simply dump TABLE_A and load it on server. One way or the other, goal is to change table data on live server.

Interesting...never heard of the MINUS keyword. I'll give that a go also (and yes - the first column is a PK - identity).
That does not work, unless OP can define his/her workstation/live server as linked server (as @Oleg pointed out)...Cite: "Keep in mind: TABLE A is on my local machine TABLE B is on the live server"

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.