0

i wanted to compare and identify certain [transaction ids] that exist in one table which is located in server 1 but does not exist in a similar transformed table on a different server (server 2). These two tables were supposed to be the same but we noticed some transaction ids were missing on the table that is located in server 2. My goal is to identify all the missing transaction ids. The query that i am looking to write is as below

SELECT
    server_1.table_1.[transaction_id] from server_1.table_1 
JOIN 
    server_2.table_2 ON
Table_1.[transaction_id] = Table_2.[transaction_id] 
WHERE 
server_1.table_1.[transaction_id] NOT IN server_2.table_2.[transaction_id]

one recommendation I have received was "Let's select the IDs from the source select the result, right click and in SQL complete select general temp table script

Then you can run the scripts on the target server to create a temptable and perform your analysis

Because of the size of the table perhaps you need to do its in batches"

if anyone can help me explain this process as well please?

Thank you very much for your help in advance i am new for this , i apologize if I have not explained it enough or if i am not clear with my ask

Yonatan Mitiku
  • 1
  • 2
  • MySQL tag (which is DBMS) contradicts SSMS tag (which is a client to absolutely another DBMS). Clarify your environ. – Akina Feb 09 '23 at 07:00

1 Answers1

0

You can do that with a simple OUTER join (LEFT JOIN):

SELECT table1.transaction_id as missing_in_table2
  FROM table1 LEFT JOIN table2
  ON table1.transaction_id=table2.transaction_id 
  WHERE table2.transaction_id IS NULL
Georg Richter
  • 5,970
  • 2
  • 9
  • 15