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

I have 2 tables (srcTable1 & 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?

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->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->fetchUsercardByRecordnr($p_iRecordnr);
$iUserNumber = $aStudentcard[0][3];
$sNewDBName = $_SESSION['DB_name']."_".$iUserNumber;
// -- Then create the new database  --
$this->sPdoQuery = "CREATE DATABASE `".$sNewDBName."`;";
$this->PdoSqlReturnTrue();
// -- Create an array with the tables you want to be copied --
$aTables = array('1eTablename','2ndTablename','3thTablename');
// -- Populate the database --
for ($iIndex = 0; $iIndex < count($aTables); $iIndex++) 
{
 // -- Create the table --
    $this->sPdoQuery = "CREATE TABLE `".$sNewDBName."`.`".$aTables[$iIndex]."` LIKE `".$_SESSION['DB_name']."`.`".$aTables[$iIndex]."`;";
    $bSuccess = $this->PdoSqlReturnTrue();
    if(!$bSuccess ){echo("Could not create table: ".$aTables[$iIndex]."<BR>");}
    else{echo("Created the table ".$aTables[$iIndex]."<BR>");}
    // -- Fill the table --
    $this->sPdoQuery = "REPLACE `".$sNewDBName."`.`".$aTables[$iIndex]."` SELECT * FROM `".$_SESSION['DB_name']."`.`".$aTables[$iIndex]."`";
    $bSuccess = $this->PdoSqlReturnTrue();
    if(!$bSuccess ){echo("Could not fill table: ".$aTables[$iIndex]."<BR>");}
    else{echo("Filled table ".$aTables[$index]."<BR>");}
}

}

Have you tried SELECT DISTINCT ?

INSERT INTO destTable
SELECT DISTINCT * FROM srcTable

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`)

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.

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

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.

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

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