| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169 |
- --0.备份当前db的client表到client_back中;
- select [id]
- ,[name1]
- ,[name2]
- ,[phone1]
- ,[phone2]
- ,[qq1]
- ,[qq2]
- ,[addr1]
- ,[addr2]
- ,[occupation1]
- ,[occupation2]
- ,[birthday1]
- ,[birthday2]
- ,[time3]
- ,[area]
- ,[area2]
- ,[check1]
- ,[check2]
- ,[check3]
- ,[sex]
- ,[zodiac]
- ,[photo] into [db].[dbo].[client_back] from [db].[dbo].[client]
- go
- --1.将db的client表全部记录插入到#tempall表中;
- select [id]
- ,[name1]
- ,[name2]
- ,[phone1]
- ,[phone2]
- ,[qq1]
- ,[qq2]
- ,[addr1]
- ,[addr2]
- ,[occupation1]
- ,[occupation2]
- ,[birthday1]
- ,[birthday2]
- ,[time3]
- ,[area]
- ,[area2]
- ,[check1]
- ,[check2]
- ,[check3]
- ,[sex]
- ,[zodiac]
- ,[photo] into #tempall from [db].[dbo].[client]
- go
- insert into #tempall select [id]
- ,[name1]
- ,[name2]
- ,[phone1]
- ,[phone2]
- ,[qq1]
- ,[qq2]
- ,[addr1]
- ,[addr2]
- ,[occupation1]
- ,[occupation2]
- ,[birthday1]
- ,[birthday2]
- ,[time3]
- ,[area]
- ,[area2]
- ,[check1]
- ,[check2]
- ,[check3]
- ,[sex]
- ,[zodiac]
- ,[photo] from [2014].[dbo].[client]
- go
- --2.过滤掉完全重复的记录;
- --select distinct * into #tempdis from #tempall
- --go
- --drop table #tempall
- --go
- --此步省略……
- --3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
- select identity(int,1,1) as autoid, * into #temp1 from #tempall
- go
- select min(autoid) as autoid into #temp2 from #temp1 group by id
- go
- select [id]
- ,[name1]
- ,[name2]
- ,[phone1]
- ,[phone2]
- ,[qq1]
- ,[qq2]
- ,[addr1]
- ,[addr2]
- ,[occupation1]
- ,[occupation2]
- ,[birthday1]
- ,[birthday2]
- ,[time3]
- ,[area]
- ,[area2]
- ,[check1]
- ,[check2]
- ,[check3]
- ,[sex]
- ,[zodiac]
- ,[photo]
- into [db].[dbo].[client#] from #temp1 where autoid in(select autoid from #temp2) order by id
- go
- --4.将过滤好的记录重新插入原表;
- drop table #tempall
- drop table #temp1
- drop table #temp2
- truncate table [db].[dbo].[client]
- go
- insert into [db].[dbo].[client]([id]
- ,[name1]
- ,[name2]
- ,[phone1]
- ,[phone2]
- ,[qq1]
- ,[qq2]
- ,[addr1]
- ,[addr2]
- ,[occupation1]
- ,[occupation2]
- ,[birthday1]
- ,[birthday2]
- ,[time3]
- ,[area]
- ,[area2]
- ,[check1]
- ,[check2]
- ,[check3]
- ,[sex]
- ,[zodiac]
- ,[photo])
- select [id]
- ,[name1]
- ,[name2]
- ,[phone1]
- ,[phone2]
- ,[qq1]
- ,[qq2]
- ,[addr1]
- ,[addr2]
- ,[occupation1]
- ,[occupation2]
- ,[birthday1]
- ,[birthday2]
- ,[time3]
- ,[area]
- ,[area2]
- ,[check1]
- ,[check2]
- ,[check3]
- ,[sex]
- ,[zodiac]
- ,[photo] from [db].[dbo].[client#]
- go
- drop table [db].[dbo].[client#]
- go
- --5.结束;
|
