--- In firebird-support@yahoogroups.com, "josephhirn"
<josephhirn@y...> wrote:

> Basically i need to loop through a set of document_page records using
> the id and document_id of each record to insert a corresponding record
> into endorsement using the document_page's id and document_id (id and
> document_id are the concatinated key).
>
> I would like to have seen constraints manage this, but we didn't and I
> need a clean up script.
>
> Basically i need :
>

Joseph,

As Nick said, For / Select syntax is actually inside a language called
PSQL. This language is what Firebird uses for stored procedures and
triggers. Scripts are not stored procedures, they are a bunch of sql
commands. But for what you want, this is how you can do it.

1. Create a temporary stored procedure with the cleanup code
2. Run the stored procedure
3. Drop the stored procedure

Oh, and dont forget the semi colon after the insert statement in the
for loop, and commit between each step, because Firebird has been
known to do some funny things if you make a DDL change, then do some
DML without committing first.

-----

SET TERM ^;

CREATE PROCEDURE TMP_CLEANUPSCRIPT
AS
BEGIN

for select id, document_id from docpage
where (...)
into :id, :document_id
do begin
insert into endorsement (id, document_id,...)
values (:id,:document_id,...);
end

END
^

SET TERM ;
^

COMMIT WORK;

EXECUTE PROCEDURE TMP_CLEANUPSCRIPT;

COMMIT WORK;

DROP PROCEDURE TMP_CLEANUPSCRIPT;

COMMIT WORK;

Thats it.

Adam