Oops, it seems, I can not attach any files
===
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
===
Am 04.04.2014 18:52, schrieb Thomas Beckmann:
>
>
> 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]
>
>
--
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.
