How to insert Distinct Records from Table A to Table B (both tables have same structure)

Question

I want to insert only Distinct Records from Table "A" to Table "B". Assume both the tables has same structure.

Friarbird

By Distinct do you mean only records that don't exist in table B? Do you mean records that only occur once in Table A? Be more specific, and tell us what RDBMS you are using. – Divulge

Answer 1

If by DISTINCT you mean unique records that are on TableB that aren't already in TableA, then do the following:

INSERT INTO TableB(Col1, Col2, Col3, ... , Coln)
SELECT DISTINCT A.Col1, A.Col2, A.Col3, ... , A.Coln
FROM TableA A
LEFT JOIN TableB B
ON A.KeyOfTableA = B.KeyOfTableB
WHERE B.KeyOfTableB IS NULL

Lardon

Answer 2

INSERT INTO B SELECT DISTINCT * FROM A

You might not want the id column of the table to be part of the distinct check, so use this solution if that's the case: https://mcmap.net/q/1395942/-how-to-insert-distinct-records-from-table-a-to-table-b-both-tables-have-same-structure

Succussion

Answer 3

INSERT INTO TableB
    (Col1, Col2, ...)
    SELECT DISTINCT Col1, Col2, ...
        FROM TableA

Polinski

Yes. This is the query to use. Not the one by TheJubilex. – Insouciance

Answer 4

    INSERT INTO TableB
            SELECT *
            FROM TableA AS A
            WHERE NOT EXISTS(SELECT * FROM TableB AS B WHERE B.Field1 = A.Field1) 
-- If need: B.Field2 = A.Field2 and B.Field3 = A.Field3

Isola