How to compare two tables without unique identifier in SQL-CodePudding

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

CodePudding user response：

You can, actually, work with the EXCEPT table operator: Like here:

WITH 
old_table(cust,accnt,addr) AS (
            SELECT 'AAAA',101,'AA1'
  UNION ALL SELECT 'BBBB',102,'BB1'
  UNION ALL SELECT 'CCCC',102,'BB1'
)
,
new_table AS (
            SELECT 'AAAA',101,'AA1'
  UNION ALL SELECT 'BBBB',102,'BB1'
  UNION ALL SELECT 'CCCC',102,'BB2'
)
(
SELECT * FROM old_table
EXCEPT
SELECT * FROM new_table
)
UNION ALL 
(
SELECT * FROM new_table
EXCEPT
SELECT * FROM old_table
)
;
-- out  cust | accnt | addr 
-- out ------ ------- ------
-- out  CCCC |   102 | BB1
-- out  CCCC |   102 | BB2

CodePudding user response：

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