Copy Rows from One Table to Another, Ignoring Duplicates

Copy rows from one table to another, ignoring duplicates

Your problem is that you need another where clause in the subquery that identifies what makes a duplicate:

INSERT INTO destTable
SELECT Field1,Field2,Field3,... 
FROM srcTable
WHERE NOT EXISTS(SELECT * 
                 FROM destTable 
                 WHERE (srcTable.Field1=destTable.Field1 and
                       SrcTable.Field2=DestTable.Field2...etc.)
                 )

As noted by another answerer, an outer join is probably a more concise approach. My above example was just an attempt to explain using your current query to be more understandible. Either approach could technically work.

INSERT INTO destTable
SELECT s.field1,s.field2,s.field3,... 
FROM srcTable s 
       LEFT JOIN destTable d ON (d.Key1 = s.Key1 AND d.Key2 = s.Key2 AND...)
WHERE d.Key1 IS NULL

Both of the above approaches assume you are woried about inserting rows from source that might already be in destination. If you are instead concerned about the possibility that source has duplicate rows you should try something like.

INSERT INTO destTable
SELECT Distinct field1,field2,field3,... 
FROM srcTable  

One more thing. I'd also suggest listing the specific fields on your insert statement instead of using SELECT *.

Copy data from one table to another - Ignore duplicates Postgresql

Assuming id is your primary key, and table structures are identical(both table has common columns as number of columns and data type respectively), use not exists :

insert into TableB
select * 
  from TableA a 
 where not exists ( select 0 from TableB b where b.id = a.id )

Copy rows from one table to another, ignoring duplicates

try to change where condition from (T2.screenname = T1.screenname,T2.list_id = T1.list_id) to (T2.screenname = T1.screenname AND T2.list_id = T1.list_id)

(note AND keyword instead of comma)

How to copy records from one table to another without duplicates in MySQL where UNIQUE KEY is set

Simplest way seems to be

INSERT IGNORE ... INTO table2 SELECT * FROM table1;

The unique constraint on table2 will silently drop all duplicate rows, and you will end up inserting only nonduplicate rows according to your indexes. Whether this is the desired behaviour or not, only you can say; your question does not specify this clearly.

To be clearer, in your setup say you have,

1, 5259, 25, 226, '4'
...
123, 5259, 25, 226, '12'

The first row will be inserted (and you'll have a '4' in fifth column), the 123rd will be ignored. Actually, in MySQL there's no hard guarantee on which row will be inserted among those that have the same indexed columns. It usually is the first, but it might not be. If you need to specify that, i.e., which criteria to insert either the 1st or 123rd row, then the correct answer is - as always :-) - Gordon Linoff's.

Insert rows from one table to another but only those rows that have no duplicates

You can use also not in in subselect

INSERT INTO Table2(c, a, b, rc, bid)
SELECT  c, a, b, rc, bid
FROM Table1 t1
WHERE (c,a,b) not in ( SELECT c,a,b 
      FROM Table1 t2
      GROUP BY c, a, b
     HAVING COUNT(*) > 1
 )

Copy records from one table to another without duplicates

This would create rows with unique fname and take Win7 if both Win7 and XP existed.

INSERT INTO #B
SELECT a.fname, MIN(a.lname) 
FROM #A a
GROUP BY a.fname

---

Related Topics