Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I find out the differences in data between the two tables that have exact schema, and how to produce synchronisation SQL to get the union results (without duplicates) ?

These are the 2 tables:

SOURCE01.dbo.Customers (31,022 rows)

TARGET01.dbo.Customers (29,300 rows)

The schema of each table is :

  • [CustomerId] : nvarchar(255)
  • [CustomerSerializedProfile]: nvarchar(max)
  • [CreatedDatetime] : DateTime
share|improve this question

Using Native tools :

tablediff : the tablediff utility compares the data in the source table to the table in the destination table.

powershell : compare-object allows you to achieve that. here is a good example

third party:

redgate schema and data compare. You can even use powershell and schema/data compare to automate things.

share|improve this answer

I've used this one recently for a similar purpose:

select
    s.*
    ,t.*
from SOURCE01.dbo.Customers as s
full outer join TARGET01.dbo.Customers as t
    on s.CustomerId = t.CustomerId
where s.CustomerSerializedProfile <> t.CustomerSerializedProfile
or s.CreatedDatetime <> t.CreatedDatetime
or s.CustomerId is NULL
or t.CustomerId is NULL;

(Code edited post comment; apologies, I was writing it without an instance to hand.)

It does rely on the primary key being consistent. But you have to have something consistent after all. A meta script to generate code like the above is relatively easy to write and makes many-column tables easy to compare.

As for synchronisation you will have to source left join target and target left join source, then decide what you want to do with the result of each.

share|improve this answer

Other than tablediff and powershell mentioned in the previous answers, you can also use SQL with the UNION ALL statement to find the records that don’t match in 2 identical tables:

SELECT MIN(TableName) AS TableName
   ,ID
   ,NAME
   ,lastname
   ,Address
   ,City
FROM (
SELECT 'Table A' AS TableName
    ,Customers.id
    ,Customers.NAME
    ,Customers.lastname
    ,Customers.Address
    ,Customers.City
FROM Customers

UNION ALL

SELECT 'Table B' AS TableName
    ,CustomersOld.id
    ,CustomersOld.NAME
    ,CustomersOld.lastname
    ,CustomersOld.Address
    ,CustomersOld.City
FROM CustomersOld
) tmp
GROUP BY ID
   ,NAME
   ,lastname
   ,Address
   ,City
HAVING COUNT(*) = 1
ORDER BY id;

Another option you can try is using Data Compare in Visual Studio itself. It compares data in the source database and the target database and creates a synchronization script for the tables you’ve selected for synchronization.

And last, but not least, you can use SQL data comparison tool - ApexSQL Data Diff, to set all synchronization options, map the tables and columns with different names, create your own keys for comparison in the GUI. You can schedule it to run unattended and all you have to do is check SQL Server job history in the morning. If you need more details regarding these options, I recommend reading this article: http://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/

share|improve this answer

Somewhat surprisingly no one has mentioned that this is built into SQL Server Data Tools yet. Though the functionality is basic when compared with Redgate for example.

Some details in Compare and Synchronize Data in One or More Tables with Data in a Reference Database

share|improve this answer
    
That might be because in year May 2014, there was no SSDT ? Good that you put this answer :-) – Kin Apr 19 at 19:58

This should give you the differences between the two tables, you can then wrap this in an insert query to put the differences from A into B or vice versa.

SELECT A.CustomerId, A.CustomerSerializedProfile, A.CreatedDatetime
  FROM SOURCE01.dbo.Customers A
 WHERE NOT EXISTS (SELECT B.ID
                 FROM TARGET01.dbo.Customers
                WHERE B.CustomerId= A.CustomerId
                  AND B.CustomerSerializedProfile= A.CustomerSerializedProfile
                  AND B.CreatedDatetime= A.CreatedDatetime)
share|improve this answer

One of our free tools has a full interface for TableDiff:

http://nobhillsoft.com/Diana.aspx

Also, check out our DB Comparison tool. It’s the only one out there that compares unlimited amount of data (non of the others can do millions and millions of records) … so long as you compare between 2 servers that are linked

http://nobhillsoft.com/NHDBCompare.aspx

(we saw other links in this thread for 3rd party products so we believe its legit to mention ours... pls let us know if it isn't)

share|improve this answer
    
AFAIK it's legit as long as it as an ontopic answer to a genuine question and you state you have a connection with the product. So would have thought it's fine. – Martin Smith Apr 19 at 19:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.