教你SQL语句删除重复记录 （2）

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')

exec up_distinct 'a_dist','id'

select * from a_dist

create procedure up_distinct(@t_name varchar(30)
,@f_key varchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare @max integer,@id varchar(30) ,
@sql varchar(7999) ,@type integer
select @sql = 'declare cur_rows cursor 
for select '+@f_key+' ,count(*) from ' 
+@t_name +' group by ' +@f_key +' having count(*) > 1'
exec(@sql)
open cur_rows 
fetch cur_rows into @id,@max 
while @@fetch_status=0 
begin 
select @max = @max -1 
set rowcount @max 
select @type = xtype from syscolumns 
where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = 'delete from '+@t_name+' 
where ' + @f_key+' = '+ @id 
if @type=167
select @sql = 'delete from '+@t_name+' 
where ' + @f_key+' = '+''''+ @id +'''' 
exec(@sql)
fetch cur_rows into @id,@max 
end 
close cur_rows 
deallocate cur_rows
set rowcount 0
end

select * from systypes
select * from syscolumns where 
id = object_id('a_dist')

create table dup (id int identity not null,
name varchar(50)not null)
go
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('abc')
insert into dup(name) values ('cdefg')
insert into dup(name) values ('xyz')
insert into dup(name) values ('xyz')
go
select *
from dup
go
create table tempdb..wk(id int not null, 
name varchar(50)not null)
go
create unique index idx_remove_dup 
on tempdb..wk(name)
with IGNORE_DUP_KEY 
go
INSERT INTO tempdb..wk (id, name)
select id, name
from dup
go
select *
from tempdb..wk
go
delete from dup
go
set identity_insert dup on

INSERT INTO dup (id, name)
select id, name
from tempdb..wk
go
set identity_insert dup off
go
select *
from dup

go