insert into select复制表并去重复的方法

站长苏飞

[SQL] 纯文本查看 复制代码

INSERT newtabname NAME, sex
SELECT  NAME, sex  FROM (
SELECT DISTINCT  NAME, sex 
FROM oldtabname
  WHERE NAME='123456'
  ) AS t  WHERE t.NAME 
  NOT IN(SELECT NAME FROM  newtabname 
  WHERE t.NAME=NAME AND t.sex=sex)

代码都在上面大家自己看吧。我就是做个记录方便 查找

下面我做一下分析吧。

[SQL] 纯文本查看 复制代码

SELECT DISTINCT  NAME, sex 
FROM oldtabname
  WHERE NAME='123456'

这个是查询老表里面所有不重复的数据列

[SQL] 纯文本查看 复制代码

INSERT newtabname NAME, sex
SELECT  NAME, sex  FROM (
SELECT DISTINCT  NAME, sex 
FROM oldtabname
  WHERE NAME='123456'
  ) AS t

当然这个是将查询到的数据写入到新表

[SQL] 纯文本查看 复制代码

WHERE t.NAME 
  NOT IN(SELECT NAME FROM  newtabname 
  WHERE t.NAME=NAME AND t.sex=sex)

这是排除老表在新表中存在的数据
连接在一起就是这样了

[SQL] 纯文本查看 复制代码

INSERT newtabname NAME, sex
SELECT  NAME, sex  FROM (
SELECT DISTINCT  NAME, sex 
FROM oldtabname
  WHERE NAME='123456'
  ) AS t  WHERE t.NAME 
  NOT IN(SELECT NAME FROM  newtabname 
  WHERE t.NAME=NAME AND t.sex=sex)

大家可以做做测试有问题再讨论