lyfz_repos/经典版源码/document/数据库维护脚本/跨年转数据/2次处理转历史数据问题.sql

--背景:2015年1月1日转2014数据库造成的[db]数据库部分重要数据丢失。
--历史处理过程：
--1.处理过1次,将2014的数据转回db，但后来发现还是有遗失;
--本次为第2次处理;

--转历史数据相关的表如下:
/*
1.dindan
2.dindanjd
3.dindanbukuan
4.dindanbukuan2
5.dindansp
6.dindansp2
7.dindansp3
8.client
9.photoprint
10.burncdreg
11.digitalwork
12.mywork
13.gudingfeiyong
14.singleincome
15.singleincomemoney
16.client2phonerecord
17.client2phonerecordout
18.client3phonerecord
19.client3phonerecordout
20.employeephonerecord
21.employeephonerecordout
22.phonerecord
23.phonerecordout
24.unknownphonerecord
25.unknownphonerecordout
26.misscallrecord
27.sendreg

--其中重要的表是：
1.dindan
2.dindanjd
3.dindanbukuan
4.dindanbukuan2
5.dindansp
6.dindansp2
7.dindansp3
8.client
11.digitalwork
12.mywork
13.gudingfeiyong
14.singleincome
15.singleincomemoney
*/

/*
将[db]和[2014]中的表数据去掉标识列然后插入到临时表#temp中,再删除原表,然后重新创建原表并distinct插入回原表中
*/

--1[dindanbukuan]表;
/*
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 [db].[dbo].[dindanbukuan]

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 [2014].[dbo].[dindanbukuan]
*/
--drop table [db].[dbo].[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
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

--2[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
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[dindansp]
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#]  from [db].[dbo].[dindansp]

insert into [db].[dbo].[dindansp#]
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]

go
select distinct * into #temp from dindansp#
go
drop table dindansp#
drop table dindansp
GO
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 #temp 
go
drop table #temp 
go

--6[dindanjd]
select * into [db].[dbo].[dindanjd#] from [2014].[dbo].[dindanjd]
insert into [db].[dbo].[dindanjd#] select * from [db].[dbo].[dindanjd]
go
select distinct * into #temp from dindanjd#
go
drop table dindanjd#
drop table dindanjd
GO
CREATE TABLE [dbo].[dindanjd](
	[id] [nvarchar](50) NULL,
	[name] [nvarchar](50) NULL,
	[date] [nvarchar](50) NULL,
	[time] [nvarchar](50) NULL,
	[waiter1] [nvarchar](50) NULL,
	[waiter2] [nvarchar](50) NULL,
	[status] [nvarchar](50) NULL,
	[waiter12] [nvarchar](50) NULL,
	[waiter22] [nvarchar](50) NULL,
	[bookingdate] [nvarchar](50) NULL,
	[dress] [nvarchar](150) NULL,
	[bz] [nvarchar](150) NULL,
	[clerk] [nvarchar](120) NULL,
	[inputtime] [nvarchar](50) NULL,
	[branch] [nvarchar](50) NULL
) ON [PRIMARY]
GO
go
insert into dindanjd select * from #temp 
go
drop table #temp 
go

--7dindansp2表;
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
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

--8dindansp3表;
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
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

--9[gudingfeiyong]
select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[financecheck2],[financecheck3],[hasphoto] into [db].[dbo].[gudingfeiyong#]
from [2014].[dbo].[gudingfeiyong]

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

go
select distinct * into #temp from gudingfeiyong# 
go
drop table gudingfeiyong#
drop table gudingfeiyong
GO
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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
go
insert into gudingfeiyong([name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[financecheck2],[financecheck3],[hasphoto]) select [name],[money],[dat],[bz],[renyuan1],[renyuan2],[financecheck],[time],[financecheck2],[financecheck3],[hasphoto] from #temp 
go
drop table #temp 
go

--10[digitalwork]
select * into [db].[dbo].[digitalwork#] from [db].[dbo].[digitalwork]
insert into [db].[dbo].[digitalwork#] select * from [2014].[dbo].[digitalwork]

go
select distinct * into #temp from digitalwork# 
go
drop table digitalwork#
drop table digitalwork
GO
CREATE TABLE [dbo].[digitalwork](
	[id] [nvarchar](50) NULL,
	[waiter1] [nvarchar](50) NULL,
	[waiter2] [nvarchar](50) NULL,
	[waiter3] [nvarchar](50) NULL,
	[date1] [nvarchar](50) NULL,
	[date2] [nvarchar](50) NULL,
	[date3] [nvarchar](50) NULL
) ON [PRIMARY]
GO
go
insert into digitalwork select * from #temp 
go
drop table #temp 
go

--11[mywork]
select * into [db].[dbo].[mywork#] from [db].[dbo].[mywork] 
insert into [db].[dbo].[mywork#] select * from [2014].[dbo].[mywork] 
go
select distinct * into #temp from mywork#
go
drop table mywork#
drop table mywork
go
GO
CREATE TABLE [dbo].[mywork](
	[id] [nvarchar](50) NULL,
	[name] [nvarchar](50) NULL,
	[datetime] [nvarchar](50) NULL,
	[count1] [nvarchar](50) NULL,
	[count2] [nvarchar](50) NULL,
	[count3] [nvarchar](50) NULL,
	[type] [nvarchar](50) NULL,
	[inputdate] [nvarchar](50) NULL,
	[inputren] [nvarchar](50) NULL
) ON [PRIMARY]
GO
insert into mywork select * from #temp 
go
drop table #temp 
go