Procedure I use previously:

ALTER PROCEDURE COPY_SC_COLOR (OLD_MASTER INTEGER, NEW_MASTER INTEGER)
AS
begin
insert into SC_COLOR (SC_COLOR_SC_PID,
SC_COLOR_GROUP_CODE,
SC_COLOR_COLOR0_PID, SC_COLOR_COLOR1_PID, SC_COLOR_COLOR2_PID,
SC_COLOR_COLOR3
SC_COLOR_COLOR4_PID, SC_COLOR_COLOR5_PID, SC_COLOR_COLOR6_PID,
SC_COLOR_COLOR7
SC_COLOR_COLOR8_PID, SC_COLOR_COLOR9_PID, SC_COLOR_COLOR10_PID,
SC_COLOR_COLOR
SC_COLOR_COLOR12_PID, SC_COLOR_COLOR13_PID, SC_COLOR_COLOR14_PID,
SC_COLOR_COL
SC_COLOR_COLOR16_PID, SC_COLOR_COLOR17_PID, SC_COLOR_COLOR18_PID,
SC_COLOR_COL
SC_COLOR_COLOR20_PID, SC_COLOR_CUST_REF)
select :NEW_MASTER,
SC_COLOR_GROUP_CODE,
SC_COLOR_COLOR0_PID, SC_COLOR_COLOR1_PID, SC_COLOR_COLOR2_PID,
SC_COLOR_COLOR3
SC_COLOR_COLOR4_PID, SC_COLOR_COLOR5_PID, SC_COLOR_COLOR6_PID,
SC_COLOR_COLOR7
SC_COLOR_COLOR8_PID, SC_COLOR_COLOR9_PID, SC_COLOR_COLOR10_PID,
SC_COLOR_COLOR
SC_COLOR_COLOR12_PID, SC_COLOR_COLOR13_PID, SC_COLOR_COLOR14_PID,
SC_COLOR_COL
SC_COLOR_COLOR16_PID, SC_COLOR_COLOR17_PID, SC_COLOR_COLOR18_PID,
SC_COLOR_COL
SC_COLOR_COLOR20_PID, SC_COLOR_CUST_REF
from SC_COLOR
where SC_COLOR_SC_PID = :OLD_MASTER;
exit;
end ^

NEW_MASTER and OLD_MASTER are new and old primary key.

Regards,

Almond Wong

At 2009-8-27 08:51, you wrote:

>
>
>Dear All,
>
>I want to create a stored procedure in Firebird database to
>duplicate a record in a table.
>The parameters are: old_key and new_key.
>
>The procedure is:
>
>SELECT * WHERE primary_key = old_key INTO :var1, :var2, :var3, ...;
>
>Then I change the old_key with new_key and insert it to the table with:
>
>INSERT INTO table (field1, field2, field3, ...) values (:var1,
>:var2, :var3, ...)
>
>With those commands, I need to DECLARE all local variable first with
>type exactly the same with every fields in the table.
>And if the table have a lot of fields will make it more complicated.
>
>I usually programming using VFP, and there is a statement SCATTER that will
>copied the field value into memory variable, eg:
>
>SCATTER MEMVAR will produce m.field1, m.field2, m.field3, ... which can be
>manipulated and INSERT back into the table with INSERT FROM MEMVAR statement.
>
>I am wondering if any similar statement or perhaps a trick to do the
>job in stored procedure in Firebird?
>
>Regards,
>
>Ongky
>
>[Non-text portions of this message have been removed]
>
>

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