Hi,

Thanks for the answer.

The command is working but for just one record.

But now I like to copy more than one records.

I tried to use command:

FOR SELECT ... DO but it does not work or maybe I write the command in the wrong way.

ex:

FOR SELECT new_key, field1, field2
FROM table
WHERE key = old_key
DO
INSERT INTO table (key, field1, field2);


Any suggestion?

Regards,

Ongky


From: Helen Borrie
Sent: Thursday, August 27, 2009 8:08 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Command to duplicate a record in a table


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





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