lyfz_repos/经典版源码/document/数据库维护脚本/订单数据补全.sql

/* 两数据库进行数据对比，找到丢失的订单记录(不包含固定费用和其他收入) */
-- 方法：下列中使用curdb表示当前使用的数据库（要补全的数据库），使用olddb表示被补全的数据库（curdb里没有,olddb里有的）
use master
-- 补全订单信息;
select * from [olddb].[dbo].[dindan] where id not in (select id from [curdb].[dbo].[dindan]) order by id
insert into [curdb].[dbo].[dindan] select * from [olddb].[dbo].[dindan] where id not in (select id from [curdb].[dbo].[dindan]) order by id

-- 补全订单顾客信息;
select * from [olddb].[dbo].[client] where id not in (select id from [curdb].[dbo].[client]) order by id
insert into [curdb].[dbo].[client]([id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]) 
select [id]
      ,[name1]
      ,[name2]
      ,[phone1]
      ,[phone2]
      ,[qq1]
      ,[qq2]
      ,[addr1]
      ,[addr2]
      ,[occupation1]
      ,[occupation2]
      ,[birthday1]
      ,[birthday2]
      ,[time3]
      ,[area]
      ,[area2]
      ,[check1]
      ,[check2]
      ,[check3]
      ,[sex]
      ,[zodiac]from [olddb].[dbo].[client] where id not in (select id from [curdb].[dbo].[client]) order by id
go

-- 补全订单景点信息;
select * from [olddb].[dbo].[dindanjd] where id not in (select id from [curdb].[dbo].[dindanjd]) order by id
insert into [curdb].[dbo].[dindanjd] select * from [olddb].[dbo].[dindanjd] where id not in (select id from [curdb].[dbo].[dindanjd]) order by id

-- 补全订单商品信息;
select * from [olddb].[dbo].[dindansp] where id not in (select id from [curdb].[dbo].[dindansp]) order by id
insert into [curdb].[dbo].[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 [olddb].[dbo].[dindansp] where id not in (select id from [curdb].[dbo].[dindansp]) order by id

-- 补全订单商品修改记录信息;
select * from [olddb].[dbo].[dindansp2] where id not in (select id from [curdb].[dbo].[dindansp2]) order by id
insert into [curdb].[dbo].[dindansp2]([id],[name],[count],[date],[clerk]) 
select [id],[name],[count],[date],[clerk] from [olddb].[dbo].[dindansp2] where id not in (select id from [curdb].[dbo].[dindansp2]) order by id

-- 补全订单套系商品信息;
select * from [olddb].[dbo].[dindansp3] where id not in (select id from [curdb].[dbo].[dindansp3]) order by id
insert into [curdb].[dbo].[dindansp3]([id],[spid],[name],[count]) select [id],[spid],[name],[count] from [olddb].[dbo].[dindansp3] where id not in (select id from [curdb].[dbo].[dindansp3]) order by id

-- 补全订单补款信息;
select * from [olddb].[dbo].[dindanbukuan] where id not in (select id from [curdb].[dbo].[dindanbukuan]) order by id
insert into [curdb].[dbo].[dindanbukuan]([id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck],[discount],[time]) 
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck],[discount],[time] 
from [olddb].[dbo].[dindanbukuan] where id not in (select id from [curdb].[dbo].[dindanbukuan]) order by id

-- 补全订单补款未收信息;
select * from [olddb].[dbo].[dindanbukuan2] where id not in (select id from [curdb].[dbo].[dindanbukuan2]) order by id
insert into [curdb].[dbo].[dindanbukuan2]([id],[money],[ren],[date],[bz],[kind],[ren2]) 
select [id],[money],[ren],[date],[bz],[kind],[ren2] 
from [olddb].[dbo].[dindanbukuan2] where id not in (select id from [curdb].[dbo].[dindanbukuan2]) order by id