If you want for all records...remove the where clause!



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Yohanes Ongky
Setiadji
Sent: Thursday, August 27, 2009 05:38
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Command to duplicate a record in a table





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
<mailto:firebird-support%40yahoogroups.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]





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