sql从select插入到表中，没有重复(需要更多不同的)

I am selecting multiple rows and inserting them into another table. I want to make sure that it doesn't already exists in the table I am inserting multiple rows into. DISTINCT works when there are duplicate rows in the select, but not when comparing it to the data already in the table your inserting into.

我选择了多个行并将它们插入到另一个表中。我要确保它在我要插入多行的表中不存在。当select中有重复的行，而不是将它与插入到的表中已经存在的数据进行比较时，DISTINCT将发挥作用。

If I Selected one row at a time I could do a IF EXIST but since its multiple rows (sometimes 10+) it doesn't seem like I can do that.

如果我每次选择一行，我可以做If EXIST，但是由于它有多个行(有时是10+)，我似乎做不到。

5 个解决方案

#1

INSERT INTO target_table (col1, col2, col3)
SELECT DISTINCT st.col1, st.col2, st.col3
FROM source_table st
WHERE NOT EXISTS (SELECT 1 
                  FROM target_table t2
                  WHERE t2.col1 = st.col1 
                    AND t2.col2 = st.col2
                    AND t2.col3 = st.col3)

If the distinct should only be on certain columns (e.g. col1, col2) but you need to insert all column, you will probably need some derived table (ANSI SQL):

如果只在某些列上(例如col1、col2)，但需要插入所有列，则可能需要一些派生表(ANSI SQL):

INSERT INTO target_table (col1, col2, col3)
SELECT st.col1, st.col2, st.col3
FROM ( 
     SELECT col1, 
            col2, 
            col3, 
            row_number() over (partition by col1, col2 order by col1, col2) as rn
     FROM source_table 
) st
WHERE st.rn = 1
AND NOT EXISTS (SELECT 1 
                FROM target_table t2
                WHERE t2.col1 = st.col1 
                  AND t2.col2 = st.col2)

#2

If you already have a unique index on whatever fields need to be unique in the destination table, you can just use INSERT IGNORE (here's the official documentation - the relevant bit is toward the end), and have MySQL throw away the duplicates for you.

如果您在目标表中需要唯一的字段上已经有了唯一的索引，那么您可以使用INSERT IGNORE(这里是官方文档——相关的位在末尾)，并让MySQL为您丢弃副本。

Hope this helps!

希望这可以帮助!

#3

-1

So you're looking to retrieve all unique rows from source table which do not already exist in target table?

所以你想从源表中检索所有不存在于目标表中的唯一行?

SELECT DISTINCT(*) FROM source
WHERE primaryKey NOT IN (SELECT primaryKey FROM target)

That's assuming you have a primary key which you can base the uniqueness on... otherwise, you'll have to check each column for uniqueness.

这是假设你有一个主键你可以把惟一性建立在。否则，您将不得不检查每一列的唯一性。

#4

-1

pseudo code for what might work

可能起作用的伪代码

    insert into <target_table> select col1 etc 
from <source_table> 
where <target_table>.keycol not in 
(select source_table.keycol from source_table)

#5

-1

There are a few MSDN articles out there about this, but by far this one is the best:

有一些MSDN关于这方面的文章，但是到目前为止，这是最好的:

http://msdn.microsoft.com/en-us/library/ms162773.aspx

They made it real easy to implement and my problem is now fixed. Also the GUI is ugly, but you actually can set minute intervals without using the command line in windows 2003.

它们使实现变得非常容易，我的问题现在得到了解决。而且GUI也很难看，但是您实际上可以在windows 2003中不使用命令行来设置时间间隔。

#1

INSERT INTO target_table (col1, col2, col3)
SELECT DISTINCT st.col1, st.col2, st.col3
FROM source_table st
WHERE NOT EXISTS (SELECT 1 
                  FROM target_table t2
                  WHERE t2.col1 = st.col1 
                    AND t2.col2 = st.col2
                    AND t2.col3 = st.col3)