lyfz_repos/经典版源码/document/数据库维护脚本/跨年转数据/将2014的数据转回当前数据库.sql

--将2014订单中的数据转回;
--1.dindan;当乱序时,不能使用insert [db].[dindan] select * from [2014].[dindan]
--insert into [db].[dbo].[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

--2.dindansp
set IDENTITY_INSERT [db].[dbo].[dindansp] ON
insert into [db].[dbo].[dindansp]
			([id]
           ,[spid]
           ,[shuliang]
           ,[kind]
           ,[price]
           ,[name]
           ,[no]
           ,[status1]
           ,[status2]
           ,[status3]
           ,[status4]
		   ,[autoid]
           ,[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 * from [2014].[dbo].[dindansp] where not exists(select id from [db].[dbo].[dindansp] where [db].[dbo].[dindansp].[id] = [2014].[dbo].[dindansp].[id])
SET IDENTITY_INSERT [db].[dbo].[dindansp] OFF
go

--3.dindanbukuan表;
--set IDENTITY_INSERT [db].[dbo].[dindanbukuan] ON
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]
where not exists(select id from [db].[dbo].[dindanbukuan] where [db].[dbo].[dindanbukuan].[id] = [2014].[dbo].[dindanbukuan].[id])
--SET IDENTITY_INSERT [db].[dbo].[dindanbukuan] OFF
go

--4.dindanbukuan2表;
--set IDENTITY_INSERT [db].[dbo].[dindanbukuan2] ON
insert into [db].[dbo].[dindanbukuan2] ([id],[money],[ren],[date],[bz],[kind],[ren2]) select [id],[money],[ren],[date],[bz],[kind],[ren2] from [2014].[dbo].[dindanbukuan2]
where not exists(select id from [db].[dbo].[dindanbukuan2] where [db].[dbo].[dindanbukuan2].[id] = [2014].[dbo].[dindanbukuan2].[id])
--SET IDENTITY_INSERT [db].[dbo].[dindanbukuan2] OFF
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])
--where id not in(select id from [db].[dbo].[client] where [db].[dbo].[client].[id] = [2014].[dbo].[client].[id])
go

--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])
go

--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])
go

--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])
go

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

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

--12.dindansp3表;
insert into [db].[dbo].[dindansp3] select * from [2014].[dbo].[dindansp3]
where not exists(select id from [db].[dbo].[dindansp3] where [db].[dbo].[dindansp3].[id] = [2014].[dbo].[dindansp3].[id])
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
go

--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
go

--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
go