Search code examples
sqlsql-servercomparison

How to compare two tables without unique identifier in SQL

enter image description here

I have two tables that have the same structure without a unique identifier. How to compare these two tables

I try to use row numbers to compare them. The code is as the following

WITH source AS  
(  
     SELECT 
         ROW_NUMBER() OVER(ORDER BY Customer Account    address) AS RowN,   
         Customer Account address
     FROM 
         old
)  

WITH target AS  
(  
     SELECT 
         ROW_NUMBER() OVER(ORDER BY Customer Account    address) AS RowN,   
         Customer Account address
     FROM 
         New
)  

SELECT 
    s.address, t.address 
FROM
    source s
JOIN
    traget t ON s.RowN = t.RowN
WHERE
    s.Customer != t.Customer
    OR s.Account != t.Account
    OR s.address != t.address

Except result:

s.address   t.address
---------------------
BB1         BB2

But I get an error

Incorrect syntax near the keyword 'WITH'

Microsoft SQL Server version: Microsoft SQL Server 2017

Solution

  • you are missing alot of commas ;)

    WITH source AS  
(  
     SELECT ROW_NUMBER() OVER(ORDER BY Customer ,Account, address) AS RowN,   
    Customer,    Account ,address
FROM old
)  
, target AS  
(  
     SELECT ROW_NUMBER() OVER(ORDER BY Customer, Account ,address) AS RowN,   
    Customer,    Account, address
FROM New
)  

Select s.address , t.address 
from source s
join traget t on s.RowN =t.RowN
where s.Customer <> t.Customer
or s.Account <> t.Account
or s.address <> t.address