atunccekic wrote:
I want to create monthly backups of some tables by executing a procedure.
For example, lets say I have a table named as COMPANY, and every month I want to create a backup table named as COMPANY_BCK_2014_01, COMPANY_BCK_2014_02 and copy the existing data into these backup tables.
Can I create a table in a stored procedure by using the DDL of an existing table?
Thomas Beckmann answers:
Consider this procedure. It does not take care of calculated fields, though. That can easily be added. You might consider to add the feature of executing the statements on another, possibly remote, database/server ("execute statement on external"), but you've to be aware of possible performance issues during export.
create or alter procedure P_CPYTBL (
TBL varchar(31),
SUFFIX varchar(10))
as
declare variable CRT_STMT varchar(16000);
declare variable INS_STMT varchar(16000);
begin
for with recursive
CTE_FLD as (select
trim(rf.RDB$RELATION_NAME) as TBL,
' ' || cast(trim(rf.RDB$FIELD_NAME) as varchar(16000)) as FLD,
' ' || trim(rf.RDB$FIELD_NAME) || ' ' ||
case f.RDB$FIELD_TYPE
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char('||f.RDB$FIELD_LENGTH||')'
when 16 then iif(f.RDB$FIELD_SUB_TYPE = 1,
'numeric('||f.RDB$FIELD_PRECISION||','||(-1*f.RDB$FIELD_SCALE)||')',
'bigint')
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar('||f.RDB$FIELD_LENGTH||')'
when 40 then 'cstring('||f.RDB$FIELD_LENGTH||')'
when 261 then 'blob' || coalesce(' sub_type
'||f.RDB$FIELD_SUB_TYPE, '') || coalesce(' segment size
'||f.RDB$SEGMENT_LENGTH, '')
end || coalesce(' ' || coalesce(rf.RDB$DEFAULT_SOURCE,
f.RDB$DEFAULT_SOURCE), '') as DECL,
rf.RDB$FIELD_POSITION + 1 as NXT_POS
from RDB$RELATION_FIELDS rf
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
left join RDB$RELATIONS r on r.RDB$RELATION_NAME =
trim(rf.RDB$RELATION_NAME) || :SUFFIX
where rf.RDB$FIELD_POSITION = 0 and rf.RDB$RELATION_NAME = :TBL
and r.RDB$RELATION_ID is null
union all select
f0.TBL,
f0.FLD || ',' || ascii_char(10) || ' ' ||
trim(rf.RDB$FIELD_NAME) as FLD,
f0.DECL || ',' || ascii_char(10) ||
' ' || trim(rf.RDB$FIELD_NAME) || ' ' ||
case f.RDB$FIELD_TYPE
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char('||f.RDB$FIELD_LENGTH||')'
when 16 then iif(f.RDB$FIELD_SUB_TYPE = 1,
'numeric('||f.RDB$FIELD_PRECISION||','||(-1*f.RDB$FIELD_SCALE)||')',
'bigint')
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar('||f.RDB$FIELD_LENGTH||')'
when 40 then 'cstring('||f.RDB$FIELD_LENGTH||')'
when 261 then 'blob' || coalesce(' sub_type
'||f.RDB$FIELD_SUB_TYPE, '') || coalesce(' segment size
'||f.RDB$SEGMENT_LENGTH, '')
end || coalesce(' ' || coalesce(rf.RDB$DEFAULT_SOURCE,
f.RDB$DEFAULT_SOURCE), '') as DECL,
rf.RDB$FIELD_POSITION + 1 as NXT_POS
from CTE_FLD f0
join RDB$RELATION_FIELDS rf on rf.RDB$FIELD_POSITION =
f0.NXT_POS and rf.RDB$RELATION_NAME = f0.TBL
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
select first 1
'create table ' || TBL || :SUFFIX || ' (' || ascii_char(10) ||
DECL || ')' as CRT_STMT,
'insert into ' || TBL || :SUFFIX || ' (' || ascii_char(10) || FLD
|| ')' || ascii_char(10) ||
'select ' || ascii_char(10) || FLD || ascii_char(10) || ' from '
|| TBL as INS_STMT
from CTE_FLD
order by NXT_POS desc
into :CRT_STMT, :INS_STMT
do
begin
execute statement :CRT_STMT with autonomous transaction;
execute statement :INS_STMT with autonomous transaction;
end
end