--0.备份当前db的dindan表到dindan_back中;
select * into [db].[dbo].[dindan_back] from [db].[dbo].[dindan]
go

--1.将2014和db的dindan表全部记录插入到#tempall表中;
select * into #tempall from [db].[dbo].[dindan]
go

insert  #tempall 
		([id]
        ,[money1]
        ,[money2]
        ,[money3]
        ,[name1]
        ,[name2]
        ,[taoxiid]
        ,[taoxiname]
        ,[taoxijiage]
        ,[taoxizs]
        ,[time1]
        ,[time2]
        ,[time3]
        ,[bm]
        ,[ren]
        ,[discount]
        ,[payed1]
        ,[payed2]
        ,[payed3]
        ,[payed4]
        ,[payed5]
        ,[time4]
        ,[time5]
        ,[takezs]
        ,[choosezs]
        ,[status]
        ,[clothescount2]
        ,[outside2]
        ,[style]
        ,[payed3time]
        ,[payed4ren]
        ,[payed4time]
        ,[status2]
        ,[status3]
        ,[waiter1]
        ,[waiter2]
        ,[waiter3]
        ,[waiter4]
        ,[bz]
        ,[taketime]
        ,[clothescount]
        ,[outside]
        ,[bz2]
        ,[bz3]
        ,[urgent]
        ,[status4]
        ,[status5]
        ,[waiter5]
        ,[waiter6]
        ,[pinyin1]
        ,[pinyin2]
        ,[waiter7]
        ,[status6]
        ,[phone1]
        ,[phone2]
        ,[status7]
        ,[memberno]
        ,[waiter8]
        ,[time6]
        ,[status8]
        ,[bruncount]
        ,[tichenren1]
        ,[tichenren2]
        ,[tichenren3]
        ,[tichenren4]
        ,[tichenren5]
        ,[send1]
        ,[send2]
        ,[send3]
        ,[datetime4]
        ,[datetime5]
        ,[datetime6]
        ,[waiter12]
        ,[waiter13]
        ,[waiter14]
        ,[waiter22]
        ,[waiter23]
        ,[waiter24]
        ,[waiter1rate]
        ,[waiter12rate]
        ,[waiter13rate]
        ,[waiter14rate]
        ,[waiter2rate]
        ,[waiter22rate]
        ,[waiter23rate]
        ,[waiter24rate]
        ,[ren2]
        ,[renrate]
        ,[ren2rate]
        ,[delphotos]
        ,[delphotos2]
        ,[txtype]
        ,[growthtxselname]
        ,[designno]
        ,[designreplaceno]
        ,[size]
        ,[dindantype]
        ,[contractno1]
        ,[contractno2]
        ,[cfno]
        ,[calldate]
        ,[from]
        ,[reason]
        ,[time7]
        ,[time8]
        ,[time9]
        ,[time10]
        ,[authorize]
        ,[discount2]
        ,[authorize2]
        ,[bz4]
        ,[satisfaction1]
        ,[satisfaction2]
        ,[satisfaction3]
        ,[satisfaction4]
        ,[satisfaction5]
        ,[satisfaction6]
        ,[satisfaction7]
        ,[bz5]
        ,[send4]
        ,[send5]
        ,[waiter9]
        ,[curno]
        ,[xplrr]
        ,[xplrtime]
        ,[satisfaction8]
        ,[VisitPeople1]
        ,[VisitPeople2]
        ,[VisitPeople3]
        ,[VisitPeople4]
        ,[VisitPeople5]
        ,[VisitPeople6]
        ,[VisitPeople7]
        ,[VisitPeople8]
        ,[VisitTime1]
        ,[VisitTime2]
        ,[VisitTime3]
        ,[VisitTime4]
        ,[VisitTime5]
        ,[VisitTime6]
        ,[VisitTime7]
        ,[VisitTime8]) 
select [id]
        ,[money1]
        ,[money2]
        ,[money3]
        ,[name1]
        ,[name2]
        ,[taoxiid]
        ,[taoxiname]
        ,[taoxijiage]
        ,[taoxizs]
        ,[time1]
        ,[time2]
        ,[time3]
        ,[bm]
        ,[ren]
        ,[discount]
        ,[payed1]
        ,[payed2]
        ,[payed3]
        ,[payed4]
        ,[payed5]
        ,[time4]
        ,[time5]
        ,[takezs]
        ,[choosezs]
        ,[status]
        ,[clothescount2]
        ,[outside2]
        ,[style]
        ,[payed3time]
        ,[payed4ren]
        ,[payed4time]
        ,[status2]
        ,[status3]
        ,[waiter1]
        ,[waiter2]
        ,[waiter3]
        ,[waiter4]
        ,[bz]
        ,[taketime]
        ,[clothescount]
        ,[outside]
        ,[bz2]
        ,[bz3]
        ,[urgent]
        ,[status4]
        ,[status5]
        ,[waiter5]
        ,[waiter6]
        ,[pinyin1]
        ,[pinyin2]
        ,[waiter7]
        ,[status6]
        ,[phone1]
        ,[phone2]
        ,[status7]
        ,[memberno]
        ,[waiter8]
        ,[time6]
        ,[status8]
        ,[bruncount]
        ,[tichenren1]
        ,[tichenren2]
        ,[tichenren3]
        ,[tichenren4]
        ,[tichenren5]
        ,[send1]
        ,[send2]
        ,[send3]
        ,[datetime4]
        ,[datetime5]
        ,[datetime6]
        ,[waiter12]
        ,[waiter13]
        ,[waiter14]
        ,[waiter22]
        ,[waiter23]
        ,[waiter24]
        ,[waiter1rate]
        ,[waiter12rate]
        ,[waiter13rate]
        ,[waiter14rate]
        ,[waiter2rate]
        ,[waiter22rate]
        ,[waiter23rate]
        ,[waiter24rate]
        ,[ren2]
        ,[renrate]
        ,[ren2rate]
        ,[delphotos]
        ,[delphotos2]
        ,[txtype]
        ,[growthtxselname]
        ,[designno]
        ,[designreplaceno]
        ,[size]
        ,[dindantype]
        ,[contractno1]
        ,[contractno2]
        ,[cfno]
        ,[calldate]
        ,[from]
        ,[reason]
        ,[time7]
        ,[time8]
        ,[time9]
        ,[time10]
        ,[authorize]
        ,[discount2]
        ,[authorize2]
        ,[bz4]
        ,[satisfaction1]
        ,[satisfaction2]
        ,[satisfaction3]
        ,[satisfaction4]
        ,[satisfaction5]
        ,[satisfaction6]
        ,[satisfaction7]
        ,[bz5]
        ,[send4]
        ,[send5]
        ,[waiter9]
        ,[curno]
        ,[xplrr]
        ,[xplrtime]
        ,[satisfaction8]
        ,[VisitPeople1]
        ,[VisitPeople2]
        ,[VisitPeople3]
        ,[VisitPeople4]
        ,[VisitPeople5]
        ,[VisitPeople6]
        ,[VisitPeople7]
        ,[VisitPeople8]
        ,[VisitTime1]
        ,[VisitTime2]
        ,[VisitTime3]
        ,[VisitTime4]
        ,[VisitTime5]
        ,[VisitTime6]
        ,[VisitTime7]
        ,[VisitTime8] 
from [2014].[dbo].[dindan] 
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]
select [id]
        ,[money1]
        ,[money2]
        ,[money3]
        ,[name1]
        ,[name2]
        ,[taoxiid]
        ,[taoxiname]
        ,[taoxijiage]
        ,[taoxizs]
        ,[time1]
        ,[time2]
        ,[time3]
        ,[bm]
        ,[ren]
        ,[discount]
        ,[payed1]
        ,[payed2]
        ,[payed3]
        ,[payed4]
        ,[payed5]
        ,[time4]
        ,[time5]
        ,[takezs]
        ,[choosezs]
        ,[status]
        ,[clothescount2]
        ,[outside2]
        ,[style]
        ,[payed3time]
        ,[payed4ren]
        ,[payed4time]
        ,[status2]
        ,[status3]
        ,[waiter1]
        ,[waiter2]
        ,[waiter3]
        ,[waiter4]
        ,[bz]
        ,[taketime]
        ,[clothescount]
        ,[outside]
        ,[bz2]
        ,[bz3]
        ,[urgent]
        ,[status4]
        ,[status5]
        ,[waiter5]
        ,[waiter6]
        ,[pinyin1]
        ,[pinyin2]
        ,[waiter7]
        ,[status6]
        ,[phone1]
        ,[phone2]
        ,[status7]
        ,[memberno]
        ,[waiter8]
        ,[time6]
        ,[status8]
        ,[bruncount]
        ,[tichenren1]
        ,[tichenren2]
        ,[tichenren3]
        ,[tichenren4]
        ,[tichenren5]
        ,[send1]
        ,[send2]
        ,[send3]
        ,[datetime4]
        ,[datetime5]
        ,[datetime6]
        ,[waiter12]
        ,[waiter13]
        ,[waiter14]
        ,[waiter22]
        ,[waiter23]
        ,[waiter24]
        ,[waiter1rate]
        ,[waiter12rate]
        ,[waiter13rate]
        ,[waiter14rate]
        ,[waiter2rate]
        ,[waiter22rate]
        ,[waiter23rate]
        ,[waiter24rate]
        ,[ren2]
        ,[renrate]
        ,[ren2rate]
        ,[delphotos]
        ,[delphotos2]
        ,[txtype]
        ,[growthtxselname]
        ,[designno]
        ,[designreplaceno]
        ,[size]
        ,[dindantype]
        ,[contractno1]
        ,[contractno2]
        ,[cfno]
        ,[calldate]
        ,[from]
        ,[reason]
        ,[time7]
        ,[time8]
        ,[time9]
        ,[time10]
        ,[authorize]
        ,[discount2]
        ,[authorize2]
        ,[bz4]
        ,[satisfaction1]
        ,[satisfaction2]
        ,[satisfaction3]
        ,[satisfaction4]
        ,[satisfaction5]
        ,[satisfaction6]
        ,[satisfaction7]
        ,[bz5]
        ,[send4]
        ,[send5]
        ,[waiter9]
        ,[curno]
        ,[xplrr]
        ,[xplrtime]
        ,[satisfaction8]
        ,[VisitPeople1]
        ,[VisitPeople2]
        ,[VisitPeople3]
        ,[VisitPeople4]
        ,[VisitPeople5]
        ,[VisitPeople6]
        ,[VisitPeople7]
        ,[VisitPeople8]
        ,[VisitTime1]
        ,[VisitTime2]
        ,[VisitTime3]
        ,[VisitTime4]
        ,[VisitTime5]
        ,[VisitTime6]
        ,[VisitTime7]
        ,[VisitTime8]
into [db].[dbo].[dindan#] from #temp1 where autoid in(select autoid from #temp2) order by id
go


--4.将过滤好的记录重新插入原表;
drop table [db].[dbo].[dindan]
select * into [db].[dbo].[dindan] from [db].[dbo].[dindan#]
go

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