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

--0.备份当前db的dindanbukuan2表到dindanbukuan2_back中;
select [id],[money],[ren],[date],[bz],[kind],[ren2]
into [db].[dbo].[dindanbukuan2_back] from [db].[dbo].[dindanbukuan2]
go

--1.将2014和db的dindanbukuan2表全部记录插入到#tempall表中;
select [id],[money],[ren],[date],[bz],[kind],[ren2]
into #tempall from [db].[dbo].[dindanbukuan2]
go

insert into #tempall
select [id],[money],[ren],[date],[bz],[kind],[ren2]
from [2014].[dbo].[dindanbukuan2]
go

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

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

--4.将过滤好的记录重新插入原表;
drop table #tempdis
drop table #temp1
drop table #temp2
drop table [db].[dbo].[dindanbukuan2]

use [db]
CREATE TABLE [dbo].[dindanbukuan2](
	[autoid] [int] IDENTITY(1,1) NOT NULL,
	[id] [nvarchar](50) NULL,
	[money] [nvarchar](50) NULL,
	[ren] [nvarchar](50) NULL,
	[date] [nvarchar](50) NULL,
	[bz] [nvarchar](500) NULL,
	[kind] [nvarchar](50) NULL,
	[ren2] [nvarchar](50) NULL
) ON [PRIMARY]

insert into [db].[dbo].[dindanbukuan2]([id],[money],[ren],[date],[bz],[kind],[ren2]) 
select [id],[money],[ren],[date],[bz],[kind],[ren2] 
from [db].[dbo].[dindanbukuan2#]

drop table [db].[dbo].[dindanbukuan2#]
--5.结束;