At 10:51 AM 27/08/2009, 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?

This is unnecessarily complicated, since you can do it directly with an insert statement:

INSERT INTO Atable (keyfield, field1, field2, field3, ...)

SELECT <constant_value_for_new_key>, field1, field2, field3, ...
from Atable where keyfield = ?

If you are using generated keys via a trigger, it is even simpler:

INSERT INTO Atable (field1, field2, field3, ...)

SELECT field1, field2, field3, ...
from Atable where keyfield = ?


./heLen