CODEWITHSUNDEEP
sql-servert-sql
AutoDev
AutoDev

Reputation: 33

Find duplicates between 2 SQL Server tables and return both values in another table and update as duplicate

I have 3 SQL Server tables. Table #1 is tblHistory, the second is tblTemp, where a daily Excel is uploaded into, and #3 is tblDuplicate.

Sample data:

tblHistory:

PostingDate  DocumentDate Reference URN   Supplier  St   Check1
-------------------------------------------------------------------------
2018-11-05    12/09/2018   12345    12345   12345    2   12/09/201812345

tblTemp:

PostingDate  DocumentDate Reference URN   Supplier  St   Check1
-------------------------------------------------------------------------
2018-10-05    12/09/2018   12345    12345   12345    2   12/09/201812345

I have written a query to return the duplicate values

select 
    PostingDate, DocumentDate, Reference, URN, St, Check1 
from 
    tblTemp
where 
    Check1 in (select Check1 from tblHistory);

but so far it is returning only one value.

I also need the query to return both values in tblDuplicate.

Please help.

Thanks

Upvotes: 2

Views: 77

Answers (1)

Samuel Pierrat
Samuel Pierrat

Reputation: 21

INSERT INTO tblDuplicate
SELECT * FROM (
      SELECT h.PostingDate, h.DocumentDate, h.Reference, h.URN, h.St, h.Check1 
      FROM tblHistory h
      WHERE h.Check1 IN(SELECT Check1 FROM tblTemp)
    UNION 
      SELECT t.PostingDate, t.DocumentDate, t.Reference, t.URN, t.St, t.Check1 
      FROM tblTemp t
      WHERE t.Check1 IN(SELECT Check1 FROM tblHistory)
    ) as AllData

With this request you return all data in tblTemp and tblHistory if data in the other table.

Upvotes: 2

Related Questions