删除MSSQL数据库表中的重复记录

方法一：

declare @max integer
declare @fieldname varchar(50)
declare cur_rows cursor local for select fieldname,count(*) from tbname group by fname having count(*) > 1
open cur_rows
fetch cur_rows into @fieldname,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from tbname where fieldname = @fieldname 
fetch cur_rows into @fieldname ,@max
end
close cur_rows
set rowcount 0

 

方法二：
   

 select distinct * into #Tmp from tableName   --用distinct排重加入临时表
    drop table tableName    --删除原表
    create table ....   ---创建原表
    select * into tableName from #Tmp   --将临时表数据加入原空表
    drop table #Tmp   --删除临时表