sp.sql

SQL中重复记录的查询和删除方法

CREATE   PROCEDURE GetDuplicates(@TableName AS VARCHAR(255), @PK AS VARCHAR(4096))
AS

declare @table_pk as varchar(5000)
declare @tmp_pk as varchar(5000)

set @table_pk = replace(@pk, ' ', '') --Remove blanks
set @tmp_pk = @table_pk

set @table_pk = @TableName+'.'+replace(@table_pk, ',', ', '+@TableName+'.') --Add table name prefix
set @tmp_pk = '#dup_table_tmp.'+replace(@tmp_pk, ',', ', #dup_table_tmp.') --Add table name prefix

declare @sql as varchar(5000)
set @sql = 'select ' + @PK
set @sql = @sql + ', count(*) AS DuplicateCount '
set @sql = @sql + 'INTO #dup_table_tmp from ' + @TableName
set @sql = @sql + ' group by ' + @PK
set @sql = @sql + ' having count(*) > 1; '

set @sql = @sql + 'select '
set @sql = @sql + dbo.GetTableFieldsList(@TableName, @TableName+'.', ' as @')
set @sql = @sql + ', #dup_table_tmp.DuplicateCount'
set @sql = @sql + ' from ' + @TableName + ' inner join #dup_table_tmp'
set @sql = @sql + ' on Checksum(' + @table_pk + ') = Checksum(' + @tmp_pk + ')'
set @sql = @sql + ' order by ' + @table_pk
--print @sql
exec (@sql)

GO


CREATE   PROCEDURE DeleteDuplicates(@TableName AS VARCHAR(255), @PK AS VARCHAR(4096))
AS

declare @sql as varchar(5000)
set @sql = 'begin transaction; '
set @sql = @sql + 'if exists (select ' + @PK + ' from ' + @TableName
set @sql = @sql + ' group by ' + @PK
set @sql = @sql + ' having count(*) > 1)'
set @sql = @sql + ' begin '
set @sql = @sql + 'select '
set @sql = @sql + dbo.GetTableFieldsList(@TableName, 'max(', ') as @')
set @sql = @sql + ' INTO #dup_table_tmp from ' + @TableName
set @sql = @sql + ' group by ' + @PK + '; '

set @sql = @sql + 'delete from ' + @TableName + '; '

set @sql = @sql + 'insert into ' + @TableName
set @sql = @sql + ' select * from #dup_table_tmp;'
set @sql = @sql + ' end '
set @sql = @sql + 'commit;'

--print @sql
exec (@sql)


GO


CREATE    Function GetTableFieldsList(
	@TableName AS VARCHAR(255),
	@FieldPrefix AS VARCHAR(255) = '',
	@FieldSuffix AS VARCHAR(255) = '')
RETURNS VARCHAR(5000)
AS

BEGIN
declare @fields_list as varchar(5000)
set @fields_list = ''

declare names_curr cursor read_only forward_only
for 
select syscolumns.name from syscolumns inner join sysobjects on syscolumns.id = sysobjects.id 
where sysobjects.name=@TableName
order by syscolumns.colid

declare @tmp_field as varchar(255)
declare @tmp_prefix as varchar(255)
declare @tmp_suffix as varchar(255)
open names_curr
fetch next from names_curr into @tmp_field
while (@@FETCH_STATUS = 0)
begin
	set @tmp_prefix = Replace(@FieldPrefix, '@', @tmp_field)
	set @tmp_suffix = Replace(@FieldSuffix, '@', @tmp_field)
	set @tmp_field = @tmp_prefix + @tmp_field + @tmp_suffix
	set @fields_list = CASE WHEN @fields_list='' THEN @tmp_field ELSE @fields_list + ', ' + @tmp_field END
	fetch next from names_curr into @tmp_field
end
close names_curr
deallocate names_curr
RETURN @fields_list
END