lyfz_repos/经典版源码/document/数据库维护脚本/跨年转数据/dindansp2过滤掉完全重复和不完全重复记录.sql

--0.备份当前db的dindansp2表到dindansp2_back
select [id],[name],[count],[date],[clerk]
into [db].[dbo].[dindansp2_back] from [db].[dbo].[dindansp2]
go

--1.将2014和db的dindnasp表的全部记录插入到#tempall中;
select [id],[name],[count],[date],[clerk]
into #tempall  from [db].[dbo].[dindansp2]
go

insert into #tempall
select [id],[name],[count],[date],[clerk] from [2014].[dbo].[dindansp2]
go

--2.过滤掉#tempall中完全重复的记录到#tempdis中;
select distinct * into #tempdis from #tempall
go

drop table #tempall
go

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as autoid, * into #temp1 from #tempdis
select min(autoid) as autoid into #temp2 from #temp1 group by [id],[name],[count],[date],[clerk]
select [id],[name],[count],[date],[clerk]
into [db].[dbo].[dindansp2#] from #temp1 where autoid in(select autoid from #temp2) order by id
go

--4.将过滤好的记录重新插入原表;
truncate table [db].[dbo].[dindansp2]
go

insert into [db].[dbo].[dindansp2]([id],[name],[count],[date],[clerk]) 
select [id],[name],[count],[date],[clerk] from [db].[dbo].[dindansp2#] 
go

drop table #tempdis 
drop table #temp1 
drop table #temp2 
drop table [db].[dbo].[dindansp2#] 
go
--5.结束