Consider attached 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.

Thomas

Am 04.04.2014 13:12, schrieb atunccekic@...:

>
>
> Thank you Thomas
>
> Sometimes I may add fields to the master table and if I write all the
> fields explicitely, I will need to modify the stored procedure. I want
> to ensure to make an exact copy. So what I want to is:
> 1- Get the structure of the master table
> 2- Create a table with the year-month-day postfix
> 3- Copy all data from the master to the backup table

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.


----------

set term ^ ;

create 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
^

set term ; ^



[Non-text portions of this message have been removed]