How to compare two tables in sql server and produce sync SQL?
How do I find out the differences in data between the two tables that have an exact schema, and how to produce synchronization 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
What is the best way to compare two tables in sql server?
You can use EXCEPT statement to compare two tables in sql server. The EXCEPT statement returns the rows from the first query (left query) that are not returned from the second query (right query). In other words, the EXCEPT statement will return the difference between two SELECT statements or tables, that helps us easily to compare the data in these tables.
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/