--插入新表
insert 新表(ks_h,ks_m,zf,mc)
select ks_h,ks_m,zf,mc from 原表 t
where not exists(select 1 from 新表
where ks_h=t.ks_h and
ks_m=t.ks_m and
zf=t.zf and
mc=t.mc
)
--删除原表中重复数据,保留ID最小的纪录
delete 原表
from 原表 t
where exists(select 1 from 原表
where ks_h=t.ks_h and
ks_m=t.ks_m and
zf=t.zf and
mc=t.mc and
ID<t.ID
)
insert 新表(ks_h,ks_m,zf,mc)
select ks_h,ks_m,zf,mc from 原表 t
where not exists(select 1 from 新表
where ks_h=t.ks_h and
ks_m=t.ks_m and
zf=t.zf and
mc=t.mc
)
--删除原表中重复数据,保留ID最小的纪录
delete 原表
from 原表 t
where exists(select 1 from 原表
where ks_h=t.ks_h and
ks_m=t.ks_m and
zf=t.zf and
mc=t.mc and
ID<t.ID
)
insert into new_table(ks_h,ks_m,zf,mc) select ks_h,ks_m,zf,mc from old_table t
where not exists
(select * from new_table where ks_h=t.ks_h and ks_m=t.ks_m and zf=t.zf and mc=t.mc )
--删除原表中重复数据:
select ks_h ks_m zf mc into #tmp from old_table group by ks_h,ks_m,zf,mc;
truncate table old_table;
insert into old_table select * from #tmp;
delete 原表
from 原表 t
where exists(select 1 from 原表
where col1=t.col1 and
col2=t.col2 and
ID<t.ID )
其中id 是你的自增字段,col1,col2...是你判断重复的条件
delete 原表
insert into delete 原表 ( ks_h,ks_m,zf,mc)
select ks_h,ks_m,zf,mc from newtbl