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

--select count(*) as cot from dindan where time1>='201-01-01' and time1<='2014-12-31' and status3='OK'

--跨年时,第一步将当前主数据库的记录插入到去年的数据库中;
--1.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 [back].[dbo].[dindansp] where id='111111'
SET IDENTITY_INSERT [db].[dbo].[dindansp] OFF

--2.dindanbukuan表;
set IDENTITY_INSERT [db].[dbo].[dindanbukuan] ON
insert into [db].[dbo].[dindanbukuan] ([autoid],[id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck]) select [autoid],[id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck] from [back].[dbo].[dindanbukuan] where id='111111'
SET IDENTITY_INSERT [db].[dbo].[dindanbukuan] OFF

--3.dindanbukuan2表;
set IDENTITY_INSERT [db].[dbo].[dindanbukuan2] ON
insert into [db].[dbo].[dindanbukuan2] ([autoid],[id],[money],[ren],[date],[bz],[kind],[ren2]) select [autoid],[id],[money],[ren],[date],[bz],[kind],[ren2] from [back].[dbo].[dindanbukuan2] where id='111111'
SET IDENTITY_INSERT [db].[dbo].[dindanbukuan2] OFF

--4.dindan表;
insert into [db].[dbo].[dindan] select * from [back].[dbo].[dindan] where id='111111'

--5.photoprint表;
insert into [db].[dbo].[photoprint] select * from [back].[dbo].[photoprint] where id='111111'

--6.client表;
insert into [db].[dbo].[client] select * from [back].[dbo].[client] where id='111111'

--7.burncdreg表;
insert into [db].[dbo].[burncdreg] select * from [back].[dbo].[burncdreg] where id='111111'

--8.digitalwork表;
insert into [db].[dbo].[digitalwork] select * from [back].[dbo].[digitalwork] where id='111111'

--9.mywork;
insert into [db].[dbo].[mywork] select * from [back].[dbo].[mywork] where id='111111'

--10.dindanjd表;
insert into [db].[dbo].[dindanjd] select * from [back].[dbo].[dindanjd] where id='111111'

--11.dindansp2表;
insert into [db].[dbo].[dindansp2] select * from [back].[dbo].[dindansp2] where id='111111'

--12.dindansp3表;
insert into [db].[dbo].[dindansp3] select * from [back].[dbo].[dindansp3] where id='111111'


--跨年时,第二步当第一步成功时,开始删除当前数据库里的记录;
--1.dindan表;
delete from dindan where id = '' ;
--2.dindansp表;
delete from dindansp where id = '' ;
--3.photoprint表
delete from photoprint where id = ''
--4.dindanbukuan表
delete from dindanbukuan where id = ''
--5.dindanbukuan2表
delete from dindanbukuan2 where id = ''
--6.client表
delete from client where id = ''
--7.burncdreg表
delete from burncdreg where id = ''
--8.digitalwork表
delete from digitalwork where id = ''
--9.mywork表
delete from mywork where id = ''
--10.dindansp表
delete from dindansp where id =''
--11.dindansp2表
delete from dindansp2 where id = ''
--12.dindansp3表
delete from dindansp3 where id = ''


--跨年时,第三步处理账务方面;

--1.gudingfeiyong表,支出;
--1.1.获取gudingfeiyong表的所有记录;
select count(*) as cot from gudingfeiyong where dat >= '2014-01-01' and dat <= '2014-12-31'
--1.2.将当前数据库的数据插入到跨年数据库中;
insert into [db].[dbo].[gudingfeiyong] select * from [back].[dbo].[gudingfeiyong] where id='111111'
--1.3.删除当前数据库的记录;
delete from gudingfeiyong where id ='' 

--2.singleincome表,其他收入;
--2.1.获取singleincome表的所有记录;
select count(*) as cot from singleincome where dat >= '2014-01-01' and dat <= '2014-12-31'
--2.2.将当前数据库的数据插入到跨年数据库中;
insert into [2014].[dbo].[singleincome] select * from [db].[dbo].[singleincome] where id = ''
insert into [2014].[dbo].[singleincomemoney] select * from [db].[dbo].[singleincomemoney] where id = ''
--2.3.删除当前数据库的记录;
delete from singleincome where id = ''
delete from singleincomemoney where id = ''

--3.singleincomemoney其他二销表
--3.1.获取singleincomemoney表的所有记录;
select count(*) as cot from singleincomemoney where id not in(select id from singleincome)
--3.2.将当前数据库的数据插入到跨年数据库中;
insert into [2014].[dbo].[singleincomemoney] select * from [db].[dbo].[singleincomemoney] where id =''
--3.3.删除当前数据库的记录;
delete from singleincomemoney where [id] = ''

--4.client2phonerecord表;
--4.1.获取singleincomemoney表的所有记录;
select count(*) as cot from client2phonerecord where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--4.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[client2phonerecord] select * from [%s].[dbo].[client2phonerecord] where [datetime]=''
--4.3.删除当前数据库的记录;
delete from client2phonerecord where [datetime]=''

--5.client2phonerecordout表;
--5.1.获取client2phonerecordout表的记录;
select count(*) as cot from client2phonerecordout where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--5.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[client2phonerecordout] select * from [%s].[dbo].[client2phonerecordout] where [datetime]='%s'
--5.3.删除当前数据库里的记录;
delete from  client2phonerecordout where [datetime]='%s'

--6.client3phonerecord表;
--6.1.获取client3phonerecord表的记录;
select count(*) as cot from client3phonerecord where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--6.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[client3phonerecord] select * from [%s].[dbo].[client3phonerecord] where [datetime]='%s'
--6.3.删除当前数据库里的记录;
delete from client3phonerecord where [datetime]='%s'

--7.client3phonerecordout表;
--7.1.获取client3phonerecordout表的记录;
select count(*) as cot from client3phonerecordout where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--7.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[client3phonerecordout] select * from [%s].[dbo].[client3phonerecordout] where [datetime]='%s'
--7.3.删除当前数据库里的记录;
delete from client3phonerecordout where [datetime]='%s'

--8.employeephonerecord表;
--8.1.获取employeephonerecord表的记录;
select count(*) as cot from employeephonerecord where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--8.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[employeephonerecord] select * from [%s].[dbo].[employeephonerecord] where [datetime]='%s'
--8.3.删除当前数据库里的记录;
delete from  employeephonerecord where [datetime]='%s'

--9.employeephonerecordout表;
--9.1.获取employeephonerecordout表的记录;
select count(*) as cot from employeephonerecordout where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--9.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[employeephonerecordout] select * from [%s].[dbo].[employeephonerecordout] where [datetime]='%s'
--9.3.删除当前数据库里的记录;
delete from  employeephonerecordout where [datetime]='%s'

--10.phonerecord表;
--10.1.获取phonerecord表的记录;
select count(*) as cot from phonerecord	where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--10.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[phonerecord] select * from [%s].[dbo].[phonerecord] where [datetime]='%s'
--10.3.删除当前数据库里的记录;
delete from  phonerecord where [datetime]='%s'

--11.phonerecordout表;
--11.1.获取phonerecordout表的记录;
select count(*) as cot from phonerecordout where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--11.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[phonerecordout] select * from [%s].[dbo].[phonerecordout] where [datetime]='%s'
--11.3.删除当前数据库里的记录;
delete from  phonerecordout where datetime='%s'

--12.unknownphonerecord表;
--12.1.获取unknownphonerecord表的记录;
select count(*) as cot from unknownphonerecord where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--12.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[unknownphonerecord] select * from [%s].[dbo].[unknownphonerecord] where [datetime]='%s'
--12.3.删除当前数据库里的记录;
delete from  unknownphonerecord where [datetime]='%s'

--13.unknownphonerecordout表;
--13.1.获取unknownphonerecordout表的记录;
select count(*) as cot from unknownphonerecordout where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--13.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[unknownphonerecordout] select * from [%s].[dbo].[unknownphonerecordout] where [datetime]='%s'
--13.3.删除当前数据库里的记录;
delete from unknownphonerecordout where [datetime]='%s'

--14.misscallrecord表;
--14.1.获取misscallrecord表的记录;
select count(*) as cot from misscallrecord where [datetime]>= '2014-01-01 00:00:00' and [datetime] <= '2014-12-31 99:99:99'
--14.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[misscallrecord] select * from [%s].[dbo].[misscallrecord] where [datetime]='%s'
--14.3.删除当前数据库里的记录;
delete from misscallrecord where [datetime]='%s'

--15.sendreg表;
--15.1.获取sendreg表的记录;
select count(*) as cot from sendreg where [timestamp]>= '20140101000000' and [timestamp] <= '20141231999999' and issended='OK'
--15.2.将当前数据库的数据插入到跨年数据库中;
insert into [%d].[dbo].[sendreg] select * from [%s].[dbo].[sendreg] where [timestamp]='%s'
--15.3.删除当前数据库里的记录;
delete from sendreg where [timestamp]='%s'