select distinct * into # from tatruncate table ta insert into ta select * from ta
一模一样?少量数据重复,先删除再insert大量数据重复select distinct * into # from tb truncate table tb insert tb select * from #
select * , id = identity(int,1,1) into tmp from tbdelete from tmp t where id not in (select min(id) from tmp where 关键字 = t.关键字) delete from tb insert into tb select *(除了id字段) from tmp drop table tmp
呵呵,删除完全重复的记录。以上楼主的方法的确不错。之前遇到过要删除某些指定字段的记录,也在这里发一下。 declare @max integer,@id integer,@idcode declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0
select distinct * into # from ta truncate table ta insert into ta select * from ta
alter table ta add id int declare @i int set @i = 1 update ta set id = @i,@i= @i + 1 delete a from ta a where exists(select 1 from id<a.id and 字段比较) alter table ta drop table id
select distinct * into # from ta truncate table ta insert into ta select * from #
declare @s varchar(8000)alter table PricesMonthly add id int,checks bigint set @s =' declare @i int set @i = 1 update PricesMonthly set id = @i,@i= @i + 1,checks = checksum(*) delete a from PricesMonthly a where exists(select 1 from PricesMonthly where id<a.id and checks=a.checks) alter table PricesMonthly drop column id,checks' exec(@s) select * from PricesMonthly
insert into ta select * from ta
truncate table tb
insert tb select * from #
delete from tb
insert into tb select *(除了id字段) from tmp
drop table tmp
declare @max integer,@id integer,@idcode
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
insert into ta select * from ta
declare @i int
set @i = 1
update ta set id = @i,@i= @i + 1
delete a from ta a where exists(select 1 from id<a.id and 字段比较)
alter table ta drop table id
set @s ='
declare @i int set @i = 1
update PricesMonthly set id = @i,@i= @i + 1,checks = checksum(*)
delete a from PricesMonthly a where exists(select 1 from PricesMonthly where id<a.id and checks=a.checks)
alter table PricesMonthly drop column id,checks'
exec(@s)
select * from PricesMonthly