13

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

11 Answers 11

7

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/

| improve this answer | |
6

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

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

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.

| improve this answer | |
4

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;

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.

| improve this answer | |
2

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)
| improve this answer | |
1

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)

| improve this answer | |
  • 2
    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 '16 at 19:43
1

If both tables have similar primary keys, you can use the below strategy to compare source and target tables: (I marked composite key columns with an asterik)

with src as (select someCol1*, 
                    someCol2*, 
                    someCol3, 
                    someCol4, 
                    someCol5
             from src_table),

tgt as (select someCol1NameCouldDiffer* as someCol1, 
               someCol2*, 
               someCol3, 
               someCol4, 
               someCol5
        from tgt_table),

--Find which keys have at least 1 non-key column difference:

diffs as (select someCol1, 
                 someCol2 
          from (select all 5 columns 
                from src 
                **union** 
                select all 5 columns 
                from target ) 
           **group by** someCol1, someCol2 
           **having count(*)>1** 

--Reselect all columns you wish to compare from src union target, 
--joining on the keys from "diffs" above to show only records which 
--have data differences.

select * 
from (select all 5 columns 
      from src 
      union 
      select all 5 cols 
       from tgt) t1 
join diffs on t1.someCol1 = diffs.someCol1 
           and t1.someCol2 = diffs.someCol2 
**order by ** someCol1, someCol2 desc

This works because union implicitly returns distinct records. So for any given row (identified by some key) in the source you expect to match exactly in the target, you would expect a union of the src and target to return 1 row for any given key. Thus you can use the above strategy to find out which keys return a union result having multiple rows, then query the src union target again, (this time only selecting the records having differences by joining with the diff table) selecting all columns you wish to compare, ordering by the columns composing the key, and you'll see exactly which columns are mismatched. Note the column names in the source and target need not match, as they can be aliased to each other using an "as" statement.

| improve this answer | |
0

To find the differences between two identical tables

SELECT *
FROM SOURCE01.dbo.Customers

UNION

SELECT *
FROM TARGET01.dbo.Customers

EXCEPT

SELECT *
FROM SOURCE01.dbo.Customers

INTERSECT

SELECT *
FROM TARGET01.dbo.Customers


The order of operations causes the INTERSECT to be performed first which will give you a dataset of only rows that exist in both tables. Second the UNION is performed which gives you all rows from both tables without duplicates. Finally, the EXCEPT is performed which deletes from your UNION (all rows of both tables) the INTERSECT dataset which is the rows in both tables. This leaves you with a dataset containing only the rows that exist in one of the tables but not in the other. If your dataset comes back empty then all rows are the same between tables.



https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql

| improve this answer | |
  • Hey there! I think your answer would be better if you used the table names from the original question! – Anthony Genovese Oct 19 '17 at 18:30
0

I had a similar problem and used the SQL 'EXCEPT' command to solve the issue. EXCEPT command takes two SELECT statements and returns the rows that are returned by the first SELECT statement (left) and not by second (right) SELECT statement.

SELECT * from table1 where x,y,z 
EXCEPT
SELECT * from table2 where a,b,c

P.S: The schema for both the tables returned by SELECT statement must match.

For more clarity, visit: Tutorials Point Page here

| improve this answer | |
0
/*
Compare master table data on 2 servers (
1. Change server name
2. Set RaceDate (@racedate) with the >, < ,= >= operator 
 before you run)

 --KNOWN ISSUES
 1. Tables need PKs

*/
SET NOCOUNT ON

--Destination Server Details
DECLARE @destServ nvarchar(40)='[sql\inst23]'    --required             -- If local instance, leave the string empty 
DECLARE @destdb nvarchar(40)='DBName'         --required        
DECLARE @destSchema nvarchar(40)='dbo'        --required        
DECLARE @destTable  nvarchar(40)='TableName'    --required      

-- Source Server Details
DECLARE @SourServ nvarchar(40)='[sql\inst07]'   --required      
DECLARE @Sourdb nvarchar(40)='DBonRemoteServer'  --required     
DECLARE @SourSchema nvarchar(40)='dbo'          --required      
DECLARE @SourTable  nvarchar(40)='TableName'      --required                                -- TableName format 'MyTable'

DECLARE @WHERE nvarchar(400) = 'WHERE 1=1'

DECLARE @Clause nvarchar(400)= 'AND Id > 201808201500000'       --Choose a Predicate to limit data --Start with AND . e.g: 'AND Date > ''20180801'' '

SELECT @WHERE = @WHERE + @Clause

DECLARE @randomtablesuffix nvarchar(5)
SELECT @randomtablesuffix= SUBSTRING(CAST(NEWID() as nvarchar(255)),1,5)


declare @v nvarchar(max), @sql nvarchar(max), @retval nvarchar(max) , @ParamDef nvarchar(400)

--GET Columns List as varchar Columns for HASHBYTES to compare
SELECT @sql='SELECT @vv= COALESCE(@vv,'''')+''CAST(ISNULL(''+ COLUMN_NAME  + '',0) as VARCHAR(''+ 
        CASE WHEN DATA_TYPE IN (''varchar'',''nvarchar'') THEN CAST(CHARACTER_MAXIMUM_LENGTH as varchar(5)) ELSE ''60 '' END +'')) + ''
from '+ @destdb + '.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='+ QUOTENAME(@destTable,'''') + ''

SET @ParamDef = N'@vv nvarchar(max) OUTPUT'
EXEC sp_executesql @sql, @ParamDef, @vv=@v OUTPUT;

SELECT @v= SUBSTRING(@v,0,LEN(@v))

--Keys to JOIN
DECLARE @pkeylistJoinOUT nvarchar(4000)=''
SET @sql='SELECT @pkeylistJoin = ISNULL(@pkeylistJoin,'''') + '' a.''+ QUOTENAME(COLUMN_NAME) + ''=b.''+ QUOTENAME(COLUMN_NAME) + '' AND'' 
    FROM '+@destdb+'.[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
    WHERE TABLE_NAME='+ QUOTENAME(@destTable,'''') + ' ORDER BY ORDINAL_POSITION'

SET @ParamDef = N'@pkeylistJoin nvarchar(max) OUTPUT'
EXEC sp_executesql @sql, @ParamDef, @pkeylistJoin=@pkeylistJoinOUT OUTPUT;  

SELECT @pkeylistJoinOUT = REPLACE(REPLACE(REVERSE( SUBSTRING(REVERSE(@pkeylistJoinOUT), CHARINDEX(']', REVERSE(@pkeylistJoinOUT)), LEN(@pkeylistJoinOUT)) ),']',''),'[','')


--Get Column List 

DECLARE @ColumnListOut nvarchar(max)=''
SET @sql='SELECT  @ColumnList=ISNULL(@ColumnList,'''') + COLUMN_NAME + '',''  FROM '+@destdb +'.[INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME='+QUOTENAME(@destTable,'''')+ ' ORDER BY ORDINAL_POSITION'

SET @ParamDef = N'@ColumnList nvarchar(max) OUTPUT'
EXEC sp_executesql @sql, @ParamDef, @ColumnList=@ColumnListOut OUTPUT;  


SET @ColumnListOut=SUBSTRING(@ColumnListOut,0,LEN(@ColumnListOut))

--Now Compare

SELECT @sql='

SELECT a.* INTO ##_destissues'+@randomtablesuffix+' FROM (
SELECT HASHBYTES (''SHA2_512'','+ @v +')HashVal,'+ @ColumnListOut +' FROM '+@destServ+'.'+@destdb+'.'+@destSchema+'.'+@destTable + ' x WITH (NOLOCK) ' + @WHERE + '
)a 
JOIN (
SELECT HASHBYTES (''SHA2_512'','+@v +')HashVal,'+ @ColumnListOut + ' FROM ' +@SourServ +'.'+ @Sourdb+ '.'+@SourSchema+'.'+ @SourTable +' y WITH (NOLOCK)  ' + @WHERE + '
)
b ON '+@pkeylistJoinOUT + ' AND  a.HashVal <> b.HashVal '

--print @sql

exec (@sql)


SELECT @sql='

SELECT b.* INTO ##_sourceissues'+@randomtablesuffix+ ' FROM (
SELECT HASHBYTES (''SHA2_512'','+ @v +')HashVal,'+ @ColumnListOut +' FROM '+@destServ+'.'+@destdb+'.'+@destSchema+'.'+@destTable + ' x WITH (NOLOCK) ' + @WHERE + '
)a 
JOIN (
SELECT HASHBYTES (''SHA2_512'','+@v +')HashVal,'+ @ColumnListOut + ' FROM ' +@SourServ +'.'+ @Sourdb+ '.'+@SourSchema+'.'+ @SourTable +' y WITH (NOLOCK)  ' + @WHERE + '
)
b ON '+@pkeylistJoinOUT + ' AND  a.HashVal <> b.HashVal '


exec (@sql)

--Get Column List for Pivoting
DECLARE @ColumnListOutasVC nvarchar(max)=''

SET @sql='SELECT  @ColumnList=ISNULL(@ColumnList,'''')+  ''CAST(''+ COLUMN_NAME + '' AS VARCHAR(200)) as ''+ COLUMN_NAME + '',''   FROM ' + @destdb+'.[INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME='+QUOTENAME(@desttable,'''')


SET @ParamDef = N'@ColumnList nvarchar(max) OUTPUT'
EXEC sp_executesql @sql, @ParamDef, @ColumnList=@ColumnListOutasVC OUTPUT;  

SET @ColumnListOutasVC=SUBSTRING(@ColumnListOutasVC,0,LEN(@ColumnListOutasVC))

--Get PKs as VARCHAR Values

DECLARE @pkeylistJoinOUTVC nvarchar(4000)=''

SET @sql='SELECT @pkeylistJoin = ISNULL(@pkeylistJoin,'''') + ''CAST(''+COLUMN_NAME + '' as varchar(200)) as '' + COLUMN_NAME + ''1,''  FROM '+ @destdb+'.[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]   WHERE TABLE_NAME='+QUOTENAME(@destTable,'''') + '  ORDER BY ORDINAL_POSITION'

SET @ParamDef = N'@pkeylistJoin nvarchar(max) OUTPUT'
EXEC sp_executesql @sql, @ParamDef, @pkeylistJoin=@pkeylistJoinOUTVC OUTPUT;    
SET @pkeylistJoinOUTVC=SUBSTRING(@pkeylistJoinOUTVC,0,LEN(@pkeylistJoinOUTVC))
--SELECT @pkeylistJoinOUTVC





SET @sql='
select  * INTO ##_destissuedetail'+@randomtablesuffix+ ' from(
select '+ @pkeylistJoinOUTVC + ', ' + @ColumnListOutasVC
                + '
from 
##_destissues'+ @randomtablesuffix+ '
)c UNPIVOT
(
Vals for ColNames in ('+@ColumnListOut+')
) d'

EXEC( @sql)


SET @sql='
select  * INTO ##_sourceissuedetail'+@randomtablesuffix+' from(
select '+ @pkeylistJoinOUTVC + ', ' + @ColumnListOutasVC
                + '
from 
##_sourceissues'+ @randomtablesuffix+'
)c UNPIVOT
(
Vals for ColNames in ('+@ColumnListOut+')
) d'

EXEC( @sql)

SELECT 'Tables to look for data are ##_destissuedetail'+@randomtablesuffix +' and  ##_sourceissuedetail ' +@randomtablesuffix

SET @sql='
SELECT * FROM ##_destissuedetail'+@randomtablesuffix+ '
EXCEPT
SELECT * FROM ##_sourceissuedetail' +@randomtablesuffix

EXEC (@sql)

The script (when provided with the relevant details) compares 2 tables (say Customers on server1 to Customers on Server2).

This script will be handy if you're comparing a table with many columns but struggle to find the exact mismatching column.

I have a table with 353 columns and I had to compare it to another table and find with values were not matching and this script will help you locate the exact tuple.

| improve this answer | |
-1

I think you should try xSQL Data Compare, which will do the trick in your case. Let's say for example, that you specify

SOURCE01.dbo.Customers as the **left table** and
TARGET01.dbo.Customers as the **right table**

After you compare the tables, in the comparision result, you can specify that you want to sync only the differences from the left table which would produce a SQL script to insert into TARGET01.dbo.Customers all the rows that are not in this table but exist in SOURCE01.dbo.Customers (Achieving a UNION result without duplicates). Hope this helps!

Disclosure: I'm affiliated to xSQL.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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