
--0.备份当前db的gudingfeiyong表到gudingfeiyong_back
select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[photo],[financecheck2],[financecheck3],[hasphoto],[jdd] 
into [db].[dbo].[gudingfeiyong_back] from [db].[dbo].[gudingfeiyong]
go

--1.将2014和db的gudingfeiyong表的全部记录插入到#tempall中;
select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[photo],[financecheck2],[financecheck3],[hasphoto],[jdd] 
into #tempall  from [db].[dbo].[gudingfeiyong]
go

insert into #tempall
select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[photo],[financecheck2],[financecheck3],[hasphoto],[jdd] 
from [2014].[dbo].[gudingfeiyong]
go

--2.过滤掉#tempall中完全重复的记录到#tempdis中;
--.此步省略……

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as autoid, * into #temp1 from #tempall
select min(autoid) as autoid into #temp2 from #temp1 group by [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[financecheck2],[financecheck3],[hasphoto],[jdd] 
go

select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[photo],[financecheck2],[financecheck3],[hasphoto],[jdd] 
into [db].[dbo].[gudingfeiyong#] from #temp1 where autoid in(select autoid from #temp2) order by [name]
go

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

use db
CREATE TABLE [dbo].[gudingfeiyong](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[money] [nvarchar](50) NULL,
	[dat] [nvarchar](50) NULL,
	[bz] [nvarchar](max) NULL,
	[renyuan1] [nvarchar](50) NULL,
	[renyuan2] [nvarchar](50) NULL,
	[financecheck] [nvarchar](10) NULL,
	[time] [nvarchar](50) NULL,
	[photo] [image] NULL,
	[financecheck2] [nvarchar](50) NULL,
	[financecheck3] [nvarchar](50) NULL,
	[hasphoto] [nvarchar](50) NULL,
	[jdd] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go

insert into [db].[dbo].[gudingfeiyong]([name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[photo],[financecheck2],[financecheck3],[hasphoto],[jdd]) 
select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[photo],[financecheck2],[financecheck3],[hasphoto] ,[jdd]
from [db].[dbo].[gudingfeiyong#] 
go

drop table #tempall
drop table #temp1 
drop table #temp2 
drop table [db].[dbo].[gudingfeiyong#] 
go
--5.结束