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 [email protected]: > > > 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 [email protected] <mailto:[email protected]> ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn [email protected] <mailto:[email protected]> • 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]
