SQL 将行从一个表复制到另一个表，忽略重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/656012/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy rows from one table to another, ignoring duplicates
提问by Jeff Stock
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.
我有 2 个具有相同架构的表（srcTable1 和 destTable）。我试图将所有行从 srcTable 复制到 destTable 并忽略重复项。我以为我可以添加一个带有子查询的 WHERE 子句，它只会给我不重复的行。但是，它似乎不起作用。我没有插入或选择任何行。
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?
知道我做错了什么或者你有什么更好的想法吗？
回答by JohnFx
Your problem is that you need another where clause in the subquery that identifies what makes a duplicate:
您的问题是您需要在子查询中使用另一个 where 子句来标识重复项：
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 *.
还有一件事。我还建议在插入语句中列出特定字段，而不是使用 SELECT *。
回答by Alberto Garcia
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
编辑：这适用于 MySQL 我没有在 MSSQL 上测试
回答by Alam Zaib
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`)
回答by Daniel Pratt
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
回答by Nick Josevski
Have you tried SELECT DISTINCT ?
你试过 SELECT DISTINCT 吗？
INSERT INTO destTable
SELECT DISTINCT * FROM srcTable
回答by Alemayehu Gebeyehu Desta
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 .....)
)
回答by Johan de Klijn
The solution that worked for me with PHP / PDO.
使用 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>");}
}
}
}
回答by len.sanag
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.
如果集合非常大，也许这不是最好的解决方案。
回答by Wadih M.
DISTINCT is the keyword you're looking for.
DISTINCT 是您要查找的关键字。
In MSSQL, copying unique rows from a table to another can be done like this:
在 MSSQL 中，可以像这样将唯一行从一个表复制到另一个表：
SELECT DISTINCT column_name
INTO newTable
FROM srcTable
The column_nameis the column you're searching the unique values from.
该column_name是你从搜索唯一值的列。
Tested and works.
测试和工作。
