方法3：
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')
方法4：
可以用IGNORE_DUP_KEY：
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
注释：此处delete原表，再加入不重复的值。大家也可以通过join只delete原表中重复的值。
(责任编辑：ken)