自己根据一位高人的修改写的存储过程,能删除表中的重复数据。
exec del_same_value 表名
但是有个很严重的问题。当这表中定义了不能有多个IDENTITY时,存储过程无法执行。
有待解决
create proc dbo.del_same_value
@yourtable nvarchar ( 30 )
as
declare @Fields varchar ( 8000 )
set @Fields = ''
select @Fields = @Fields + ' ,[ ' + name + ' ] ' from syscolumns
where id = object_id ( @yourtable )
set @Fields = stuff ( @Fields , 1 , 1 , '' )
exec ( ' alter table ' + @yourtable + ' add newcul int identity(1,1) ' )
exec ( ' delete ' + @yourtable + ' where newcul not in(select min(newcul) from ' + @yourtable + ' group by ' + @Fields + ' )
alter table ' + @yourtable + ' drop column newcul ' )
GO
exec del_same_value 表名
但是有个很严重的问题。当这表中定义了不能有多个IDENTITY时,存储过程无法执行。
有待解决
create proc dbo.del_same_value
@yourtable nvarchar ( 30 )
as
declare @Fields varchar ( 8000 )
set @Fields = ''
select @Fields = @Fields + ' ,[ ' + name + ' ] ' from syscolumns
where id = object_id ( @yourtable )
set @Fields = stuff ( @Fields , 1 , 1 , '' )
exec ( ' alter table ' + @yourtable + ' add newcul int identity(1,1) ' )
exec ( ' delete ' + @yourtable + ' where newcul not in(select min(newcul) from ' + @yourtable + ' group by ' + @Fields + ' )
alter table ' + @yourtable + ' drop column newcul ' )
GO