lyfz_repos/经典版源码/document/数据库维护脚本/跨年转数据/转历史数据Ver3.sql

--将2014订单中的数据转回;
--1.dindan;
insert  [db].[dbo].[dindan] 
		([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] 
where [2014].[dbo].[dindan].[id] not in(select id from [db].[dbo].[dindan])
go

--0.备份当前db的dindansp表到dindansp_back
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
into [db].[dbo].[dindansp_back] from [db].[dbo].[dindansp]

--1.将2014和db的dindnasp表的全部记录插入到#tempall中;
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
into #tempall  from [db].[dbo].[dindansp]
go

insert into #tempall
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
		   from [2014].[dbo].[dindansp]

--2.过滤掉#tempall中完全重复的记录到#tempdis中;
go
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],[spid],[name],[no]
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
into dindansp# from #temp1 where autoid in(select autoid from #temp2) order by id
go

--4.将过滤好的记录重新插入原表;
drop table [db].[dbo].[dindansp]
GO
use [db]
CREATE TABLE [dbo].[dindansp](
	[id] [nvarchar](50) NULL,
	[spid] [nvarchar](50) NULL,
	[shuliang] [nvarchar](120) NULL,
	[kind] [nvarchar](10) NULL,
	[price] [nvarchar](50) NULL,
	[name] [nvarchar](50) NULL,
	[no] [nvarchar](max) NULL,
	[status1] [nvarchar](50) NULL,
	[status2] [nvarchar](50) NULL,
	[status3] [nvarchar](50) NULL,
	[status4] [nvarchar](50) NULL,
	[autoid] [int] IDENTITY(1,1) NOT NULL,
	[date1] [nvarchar](50) NULL,
	[date2] [nvarchar](50) NULL,
	[date3] [nvarchar](50) NULL,
	[date4] [nvarchar](50) NULL,
	[name1] [nvarchar](50) NULL,
	[name2] [nvarchar](50) NULL,
	[name3] [nvarchar](50) NULL,
	[name4] [nvarchar](50) NULL,
	[hqdate] [nvarchar](50) NULL,
	[hqtime] [nvarchar](50) NULL,
	[urgent] [nvarchar](50) NULL,
	[taketime] [nvarchar](50) NULL,
	[no2] [nvarchar](max) NULL,
	[growthtxname] [nvarchar](50) NULL,
	[status5] [nvarchar](50) NULL,
	[status6] [nvarchar](50) NULL,
	[date5] [nvarchar](50) NULL,
	[date6] [nvarchar](50) NULL,
	[name5] [nvarchar](50) NULL,
	[name6] [nvarchar](50) NULL,
	[ps] [nvarchar](50) NULL,
	[zs] [nvarchar](50) NULL,
	[ren1] [nvarchar](120) NULL,
	[ren2] [nvarchar](120) NULL,
	[hqstatus] [nvarchar](50) NULL,
	[bz] [nvarchar](200) NULL
) ON [PRIMARY]
GO
go
insert into dindansp([id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] ) select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz]  from dindansp# 
go
drop table #tempdis 
drop table #temp1 
drop table #temp2 
drop table dindansp# 
go
--5.结束

--3.dindanbukuan表;
go
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck]
into [db].[dbo].[dindanbukuan#]
from [db].[dbo].[dindanbukuan]
go

insert into [db].[dbo].[dindanbukuan#]
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck] 
from [2014].[dbo].[dindanbukuan]
go

use db
go
select distinct * into #temp from [db].[dbo].[dindanbukuan#] 
go

drop table dindanbukuan#
go
drop table dindanbukuan
go

GO
use [db]
CREATE TABLE [dbo].[dindanbukuan](
	[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](max) NULL,
	[kind] [nvarchar](50) NULL,
	[ren2] [nvarchar](50) NULL,
	[item] [nvarchar](max) NULL,
	[paytype] [nvarchar](50) NULL,
	[jdd] [nvarchar](50) NULL,
	[financecheck] [nvarchar](10) NULL,
	[discount] [nvarchar](50) NULL,
	[time] [nvarchar](50) NULL
) ON [PRIMARY]
GO

go
insert into [db].[dbo].[dindanbukuan] ([id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck]) select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck] from #temp 
go
drop table #temp 
go

--4.dindanbukuan2表;
select [id],[money],[ren],[date],[bz],[kind],[ren2] into [db].[dbo].[dindanbukuan2#]
from [db].[dbo].[dindanbukuan2]

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

go
select distinct * into #temp from dindanbukuan2#
go
drop table dindanbukuan2#
drop table dindanbukuan2
GO
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]
GO
go
insert into  [db].[dbo].[dindanbukuan2]([id],[money],[ren],[date],[bz],[kind],[ren2]) select [id],[money],[ren],[date],[bz],[kind],[ren2] from #temp 
go
drop table #temp 
go

--5.photoprint表;
--insert into [db].[dbo].[photoprint] select * from [2014].[dbo].[photoprint]
--where not exists(select id from [db].[dbo].[photoprint] where [db].[dbo].[photoprint].[id] = [2014].[dbo].[photoprint].[id])

--6.client表;
insert into [db].[dbo].[client] select * from [2014].[dbo].[client] 
where not exists(select id from [db].[dbo].[client] where [db].[dbo].[client].[id] = [2014].[dbo].[client].[id])

--7.burncdreg表;
insert into [db].[dbo].[burncdreg] select * from [2014].[dbo].[burncdreg]
where not exists(select id from [db].[dbo].[burncdreg] where [db].[dbo].[burncdreg].[id] = [2014].[dbo].[burncdreg].[id])

--8.digitalwork表;
insert into [db].[dbo].[digitalwork] select * from [2014].[dbo].[digitalwork]
where not exists(select id from [db].[dbo].[digitalwork] where [db].[dbo].[digitalwork].[id] = [2014].[dbo].[digitalwork].[id])

--9.mywork;
insert into [db].[dbo].[mywork] select * from [2014].[dbo].[mywork] 
where not exists(select id from [db].[dbo].[mywork] where [db].[dbo].[mywork].[id] = [2014].[dbo].[mywork].[id])

--10.dindanjd表;
--0.备份当前db的dindanjd表到dindanjd_back中;
select * into [db].[dbo].[dindanjd_back] from [db].[dbo].[dindanjd]

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

--2.过滤掉完全重复的记录;
go
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
select [id],[name],[date],[time],[waiter1],[waiter2],[status],[waiter12],[waiter22],[bookingdate],[dress],[bz],[clerk],[inputtime],[branch]
into dindanjd# from #temp1 where autoid in(select autoid from #temp2) order by id
go
--4.将过滤好的记录重新插入原表;
drop table #tempdis
drop table #temp1
drop table #temp2
truncate table dindanjd
insert into dindanjd select * from dindanjd#
drop table dindanjd#
go
--5.结束;

--11.dindansp2表;
select * into [db].[dbo].[dindansp2#] from [db].[dbo].[dindansp2]
insert into [db].[dbo].[dindansp2#] select * from [2014].[dbo].[dindansp2]
go
select distinct * into #temp from dindansp2#
go
drop table dindansp2#
drop table dindansp2
GO
use [db]
CREATE TABLE [dbo].[dindansp2](
	[id] [nvarchar](50) NULL,
	[name] [nvarchar](50) NULL,
	[count] [nvarchar](50) NULL,
	[date] [nvarchar](50) NULL,
	[clerk] [nvarchar](50) NULL
) ON [PRIMARY]
GO
go
insert into dindansp2 select * from #temp 
go
drop table #temp 
go

--12.dindansp3表;
select * into [db].[dbo].[dindansp3#] from [db].[dbo].[dindansp3]
insert into [db].[dbo].[dindansp3#] select * from [2014].[dbo].[dindansp3]
go
select distinct * into #temp from dindansp3# 
go
drop table dindansp3#
drop table dindansp3
GO
use [db]
CREATE TABLE [dbo].[dindansp3](
	[id] [nvarchar](50) NULL,
	[spid] [nvarchar](50) NULL,
	[name] [nvarchar](50) NULL,
	[count] [nvarchar](50) NULL
) ON [PRIMARY]
GO
go
insert into dindansp3 select * from #temp 
go
drop table #temp 
go

--13.[gudingfeiyong]
set IDENTITY_INSERT [db].[dbo].[gudingfeiyong] ON
INSERT INTO [db].[dbo].[gudingfeiyong]
           ([id]
		   ,[name]
           ,[money]
           ,[dat]
           ,[bz]
           ,[renyuan1]
           ,[renyuan2]
           ,[financecheck]
           ,[time]
           ,[photo]
           ,[financecheck2]
           ,[financecheck3]
           ,[hasphoto]) select * from [2014].[dbo].[gudingfeiyong]
where not exists(select id from [db].[dbo].[gudingfeiyong] where [db].[dbo].[gudingfeiyong].[id] = [2014].[dbo].[gudingfeiyong].[id])
set IDENTITY_INSERT [db].[dbo].[gudingfeiyong] OFF

--14.[singleincome]
set IDENTITY_INSERT [db].[dbo].[singleincome] ON
insert into [db].[dbo].[singleincome]
		   ([id]
		   ,[name]
           ,[money]
           ,[dat]
           ,[bz]
           ,[renyuan1]
           ,[renyuan2]
           ,[renyuan3]
           ,[sale2type]
           ,[paytype]
           ,[phone]
           ,[payed]
           ,[balance]
           ,[pinyin]
           ,[financecheck]
           ,[count]
           ,[time]) select * from [2014].[dbo].[singleincome]
where not exists(select id from [db].[dbo].[singleincome] where [db].[dbo].[singleincome].[id] = [2014].[dbo].[singleincome].[id])
set IDENTITY_INSERT [db].[dbo].[singleincome] OFF

--15.[singleincomemoney]
set IDENTITY_INSERT [db].[dbo].[singleincomemoney] ON
insert into [db].[dbo].[singleincomemoney]
			([id]
           ,[money]
           ,[date]
           ,[clerk]
           ,[paytype]
		   ,[autoid]	
           ,[financecheck]
           ,[time]
           ,[jdd]) select * from [2014].[dbo].[singleincomemoney] 
where not exists(select id from [db].[dbo].[singleincomemoney] where [db].[dbo].[singleincomemoney].[id] = [2014].[dbo].[singleincomemoney].[id])
set IDENTITY_INSERT [db].[dbo].[singleincomemoney] OFF