• Sql Server Data Comparison
• Database Compare Sql Server
• Sql Server Management Studio

One-click re-compare - xSQL Schema Compare for SQL Server maintains a comparison session history. A session is a persisted environment that contains all the data related to a comparison operation including databases and the respective credentials, comparison options, scripting options, execution options, included or excluded object types. Table and Column mapping in Data Compare, On Completion Events to automate tasks, Object Filtering during load and all new display filters that persist even when you return to SQL Delta are just a few of the new features in version 6.

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:

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

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!

12 Answers

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.

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

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

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.

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.

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.

Edit:
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.

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

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: http://www.codeproject.com/KB/database/sqlinsertupdategenerator.aspx

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

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.

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.

what happens if you:

1. copy devTableA onto prod,
2. suspend dependencies on prodTableB
3. rename prodTable B to prodTableB1,
4. rename devTableA to prodTableB
5. reimplement dependencies on new prodTableB
6. test new prodTableB and then delete out-named old prodTableB

?

or if you have to be more respectful of the existing prodTableB and your identity column values in both dev and prod match (even if the values are not continuous)..

Sql Server Data Comparison

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.

Not the answer you're looking for? Browse other questions tagged sql-servertsqlsql-server-2008sqlcompare or ask your own question.

Database Compare Sql Server

Join the DZone community and get the full member experience.

SQL Server Management Studio (SSMS) does not offer the ability to do a schema compare between two databases. Therefore, you have two options to do a schema compare for SQL Server databases. The first is to use Visual Studio. The other is to use a third-party tool. However, there are two issues with either of those options that affect a lot of data professionals and DBAs:

1. You aren't allowed to install tools on your work machine.
2. You don't get any budget to purchase tools.

Today, I'm going to show how to do a schema compare using only SSMS, dacpac files, and the FC (File Compare) command available in Windows. As always, you're welcome.

Sql Server Management Studio

Create a DACPAC

First, create a dacpac for each database. I am going to use AdventureWorks2008 and AdventureWorks2012 as my examples for this post. You create a dacpac by right-clicking on the database name in SSMS, selecting Tasks, then Extract>, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.