Sybase IQ自定義表備份
-- 初始化配置表 www.2cto.com
if object_id('t_sz_backup_tables') is not null
drop table t_sz_backup_tables
go
create table t_sz_backup_tables(
tableName varchar(60),
datetype int,
begintime datetime null,
endtime datetime null,
status int null,
redes varchar(200) null,
indestatus int null,
order_no int null,
isused int default 1)
go
create unique index inx_uni on t_sz_backup_tables(tableName)
go
insert into t_sz_backup_tables(tableName,datetype,order_no) values('t_zbk_o2didf_ck',2,1)
-- 程序 www.2cto.com
Sql代碼
if object_id('fn_backup_tables') is not null
drop procedure fn_backup_tables
go
create procedure fn_backup_tables(
@datadate char(8),
@datetype integer,
@reccount integer output,
@usedtime integer output,
@retcode integer output,
@retdesc char(200) output)
/*--**************************************************************************************************/
-- 功能說明：
-- 讀取配置表獲取需要備份的表名，備份數據到 表名+yyyymm[dd] 表中
-- 參數說明：
-- 1、輸入
-- @datadate 會計日期
--************************************************************************************************/
as
declare @tableName varchar(60) -- 源表表名
declare @cursorcount int -- 循環總數
declare @i_count int -- 循環變量
declare @c_backupName varchar(70) -- 備份表名
declare @c_exesql varchar(1000) -- 動態語句
declare @i_zq int -- 執行周期 : 1 每天 2每月 3每季 4每年
declare @i_datetype int -- 游標變量
declare @i_errorNum int -- 錯誤數量統計
declare @dt_begintime datetime -- 處理開始時間
declare @dt_endtime datetime -- 處理結束時間
begin
--**************變量定義及初始化*************************************************************************************
-- 定義局部變量
select @dt_begintime = getdate()
-- 如果年最後一天 @i_zq = 4
if year(dateadd(day,1,@datadate))=year(@datadate)+1
select @i_zq = 4
-- 如果季度最後一天 @i_zq = 3
else if Quarter(dateadd(day,1,@datadate))<>Quarter(@datadate)
select @i_zq = 3
-- 如果月最後一天 @i_zq = 2
else if month(dateadd(day,1,@datadate))<>month(@datadate)
select @i_zq = 2
-- 否則@i_zq = 1 即每天執行
else
select @i_zq = 1
select @retcode = 0
select @i_errorNum = 0
-- 根據執行日期是否為年末、季末、月末確定需備份的表
select tableName,datetype ,rank()over(order by order_no) as rankno
into #temp
from t_sz_backup_tables
where isused = 1
and (status <> 0 or status is null)
and datetype <= @i_zq
-- 初始化循環變量
select @i_count = 1
select @cursorcount = count(1) from #temp
while (@i_count<=@cursorcount)
begin
-- 獲取表名
select @tableName = tableName,@i_datetype = datetype from #temp where rankno =
@i_count
select @reccount = 0
update t_sz_backup_tables set begintime = getdate(),status=null,endtime=null,redes=
null where tableName = @tableName
if object_id(@tableName) is not null
begin
-- 生成備份表名
if @i_datetype = 1
select @c_backupName = @tableName+'_'+@datadate
else
select @c_backupName = @tableName+'_'+substring(@datadate,1,6)
-- 備份表存在則刪除——獲取最新表結構
if object_id(@c_backupName) is not null
begin
select @c_exesql = 'drop table '+@c_backupName
execute (@c_exesql)
-- 異常檢測
select @retcode = @@error,@reccount = @reccount+@@rowcount
if @retcode<>0
begin
update t_sz_backup_tables set status=1,endtime=getdate(),redes='drop table '+@c_backupName+' failed!'
where tableName = @tableName
select @i_errorNum = @i_errorNum +1
select @i_count = @i_count + 1
continue
end
end
-- 創建備份表
select @c_exesql = 'select * into '+@c_backupName +' from '+@tableName +' where 1=2'
execute (@c_exesql)
-- 異常檢測
select @retcode = @@error,@reccount = @reccount+@@rowcount
if @retcode <>0
begin
update t_sz_backup_tables set status=1,endtime=getdate(),redes='create table '+@c_backupName+' failed!'
where tableName = @tableName
select @i_errorNum = @i_errorNum +1
select @i_count = @i_count + 1
continue
end
-- 備份數據到歷史表
select @c_exesql = 'insert into '+@c_backupName+' select * from '+@tableName
execute (@c_exesql)
-- 異常檢測
select @retcode = @@error,@reccount = @reccount+@@rowcount
if @retcode<>0
begin
update t_sz_backup_tables set status=1,endtime=getdate(),redes='insert into '+@c_backupName+' failed!'
where tableName = @tableName
select @i_errorNum = @i_errorNum +1
select @i_count = @i_count + 1
continue
end
-- 執行成功記錄日志
update t_sz_backup_tables set status=0,endtime=getdate(),redes='backup into '+@c_backupName+' success!'
where tableName = @tableName
end
else
begin
update t_sz_backup_tables set status = 1,endtime=getdate(),redes = convert(char(20),getdate(),120)+':backup failed!source table '+@tableName+' not found!'
where tableName = @tableName
select @i_errorNum = @i_errorNum + 1
select @i_count = @i_count + 1
continue
end
select @i_count=@i_count+1
end
--*******3.<操作結束,退出>*********************************************************************
select @dt_endtime = getdate()
select @usedtime = datediff(ss,@dt_begintime,@dt_endtime)
if @i_errorNum <>0
begin
select @retcode = 1
select @retdesc = convert(char,@i_errorNum)+' tables backup failed,'+convert(char,
@cursorcount-@i_errorNum)+' backup success,please check up the table t_sz_backup_tables!'
return @retcode
end
else
begin
select @retcode = 0
select @retdesc = convert(varchar,@dt_endtime,120)+'：procedure fn_backup_tables '+convert(char,@cursorcount)+' tables was backuped success！'
return @retcode
end
end
go
