Copy rows from one table to another ignoring duplicates

Question

I have 2 tables  srcTable1  amp  destTable  that have identical schemas  I am trying to copy all rows from srcTable to destTable and ignore the duplicates  I thought I could just add a WHERE clause with a subquery that would give me only the rows that aren t duplicates  However  it doesn t seem to work  I don t get any rows inserted or selected   INSERT INTO destTable SELECT   FROM srcTable WHERE NOT EXISTS SELECT   FROM destTable    I realize I could do something like this   INSERT INTO destTable SELECT   FROM srcTable WHERE MyKey IN  SELECT MyKey FROM destTable    However  my tables have multiple keys and I can t think of how you could do this with multiple keys   Any idea what I m doing wrong or do you have any better ideas

User · Accepted Answer

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 *.

User · Answer

Something like this    INSERT INTO destTable SELECT s   FROM srcTable s LEFT JOIN destTable d ON d Key1   s Key1 AND d Key2   s Key2 AND    WHERE d Key1 IS NULL

User · Answer

DISTINCT is the keyword you re looking for   In MSSQL  copying unique rows from a table to another can be done like this   SELECT DISTINCT column name INTO newTable FROM srcTable   The column name is the column you re searching the unique values from   Tested and works

User · Answer

insert into tbl2 select field1 field2     from tbl1 where not exists                 select field1 field2              from person2         where  tbl1 field1 tbl2 field1 and         tbl1 field2 tbl2 field2 and

User · Answer

The solution that worked for me with PHP   PDO   public function createTrainingDatabase  p iRecordnr      Methode  Create an database envirioment for a student by copying the original     parameter   p iRecordNumber     type integer    scope local     var   this- gt sPdoQuery           type string     scope member      var   bSuccess                  type boolean    scope local     var   aTables                   type array      scope local     var   iUsernumber               type integer    scope local     var   sNewDBName                type string     scope local     var   iIndex                    type integer    scope local      -- Create first the name of the new database --  aStudentcard    this- gt fetchUsercardByRecordnr  p iRecordnr    iUserNumber    aStudentcard 0  3    sNewDBName     SESSION  DB name        iUserNumber     -- Then create the new database  --  this- gt sPdoQuery    CREATE DATABASE     sNewDBName        this- gt PdoSqlReturnTrue       -- Create an array with the tables you want to be copied --  aTables   array  1eTablename   2ndTablename   3thTablename       -- Populate the database -- for   iIndex   0   iIndex  lt  count  aTables    iIndex           -- Create the table --      this- gt sPdoQuery    CREATE TABLE     sNewDBName        aTables  iIndex     LIKE      SESSION  DB name          aTables  iIndex             bSuccess    this- gt PdoSqlReturnTrue        if   bSuccess   echo  Could not create table     aTables  iIndex    lt BR gt          else echo  Created the table    aTables  iIndex    lt BR gt             -- Fill the table --      this- gt sPdoQuery    REPLACE     sNewDBName        aTables  iIndex     SELECT   FROM      SESSION  DB name          aTables  iIndex            bSuccess    this- gt PdoSqlReturnTrue        if   bSuccess   echo  Could not fill table     aTables  iIndex    lt BR gt          else echo  Filled table    aTables  index    lt BR gt

User · Answer

I realize this is old  but I got here from google and after reviewing the accepted answer I did my own statement and it worked for me hope someone will find it useful       INSERT IGNORE INTO destTable SELECT id  field2 field3    FROM origTable   Edit  This works on MySQL I did not test on MSSQL

User · Answer

I hope this query will help you  INSERT INTO  dTable    field1    field2   SELECT field1  field2 FROM  sTable   WHERE  sTable   field1  NOT IN  SELECT  field1  FROM  dTable

User · Answer

Have you tried SELECT DISTINCT    INSERT INTO destTable SELECT DISTINCT   FROM srcTable

User · Answer

Have you try first remove duplicates in the subquery   INSERT INTO destTable SELECT source   FROM      SELECT       FROM srcTable     EXCEPT     SELECT src   FROM     srcTable AS src     INNER JOIN destTable AS dest         put in below line the conditions to match repeated registers            ON dest SOME FANCY MATCH   src SOME FANCY MATCH AND        as source   If the sets are very large  maybe this is not the best solution

[sql] Copy rows from one table to another, ignoring duplicates

Examples related to sql

Examples related to tsql