Inserting distinct entries into the database

Question

I have two tables with exactly the same fields. Table A contains 7160 records and table B 7130 records.Now I want to insert distinct records from table A into table B such that B should not have any duplicate entry in it. How should I go about doing this?

Answer 1

You can use a "union" query to combine the results from multiple tables into a single result set. "union" will only return distinct rows from all tables.

See this page for more info:

http://www.tutorialspoint.com/mysql/mysql-union-keyword.htm

Answer 2

This basically selects records that are in A that are not in B. It would work, but you might have to tweak the field you use to uniquely identify a record. In this example I used field 'ID' but you might have to change that to A.field1 = B.field1 AND A.field2 = B.field2 etc.

INSERT INTO TABLEB
(
SELECT A.*
FROM TABLEA A
LEFT JOIN TABLEB B ON A.ID = B.ID
WHERE B.ID IS NULL
)

Answer 3

insert into tableB (id)
select t1.id from tableA t1
where t1.id not in (select t2.id from tableB t2)