Thanks,

But I don't want to copy all records in the table.


I have a "header" record with "detail" record.

Let's say:


HEADER
=================
HID | DESCRIPTION
=================
A | test 1
B | test 2


DETAIL
=================
HID | DID | TOTAL
=================
A | 1 | 50
A | 2 | 30
A | 3 | 80
B | 1 | 20
B | 2 | 10


now suppose I want to copy A to a new record with HID = "C" then the command will be:

INSERT INTO HEADER (HID, DESCRIPTION) SELECT "C", DESCRIPTION FROM HEADER;

that will work since it only one record.

but how do I copy the record from the DETAIL, since there are 3 record for "A" in DETAIL table.

Anyway I have tried to use:

FOR SELECT a.DID
FROM DETAIL a
WHERE a.HID = "A"
INTO :vDID
DO
INSERT INTO DETAIL (HID, DID, TOTAL) SELECT "C", a.DID, a.TOTAL FROM DETAIL a WHERE a.HID = "A" AND a.DID = :vDID;

and it worked.

But I am open for any suggestion.

Regards,

Ongky





From: Mercea Paul
Sent: Thursday, August 27, 2009 1:28 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Command to duplicate a record in a table


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]





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