6

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

Josh Crozier
  • 233,099
  • 56
  • 391
  • 304
Ramesh
  • 61
  • 1
  • 1
  • 2
  • 1
    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. – JNK Mar 02 '11 at 17:48

4 Answers4

9
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://stackoverflow.com/a/5171345/453673

Community
  • 1
  • 1
TheJubilex
  • 409
  • 3
  • 9
9

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
Lamak
  • 69,480
  • 12
  • 108
  • 116
5
INSERT INTO TableB
    (Col1, Col2, ...)
    SELECT DISTINCT Col1, Col2, ...
        FROM TableA
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1
    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
Alexander Brattsev
  • 896
  • 9
  • 14