FOR EXECUTE STATEMENT ON EXTERNAL to INSERT directly

[Luciano Rodrigues Nunes Mendes]

Hi Guys,

Is there any way to insert data into a table through a query in an external database (FOR EXECUTE STATEMENT ON EXTERNAL) directly without having to use INTO...DO for variables and only then insert into the table?

Something like 6.2.2. INSERT …​ SELECT

I need to move data from an external database to the current database and I wouldn't like to have to create variables for each column of each table (INTO...DO) and then insert the data into the table.

Thanks in advanced,

Luciano

[Mark Rotteveel]

On 27-05-2022 02:43, Luciano Rodrigues Nunes Mendes wrote:
> Is there any way to insert data into a table through a query in an
> external database (FOR EXECUTE STATEMENT ON EXTERNAL) directly without
> having to use INTO...DO for variables and only then insert into the table?
>
> Something like 6.2.2. INSERT …​ SELECT

> <https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-dml-insert-select>

>
> I need to move data from an external database to the current database
> and I wouldn't like to have to create variables for each column of each
> table (INTO...DO) and then insert the data into the table.

There is no solution where you don't have to create the necessary local
variables.

The only choices you have is between selecting from the remote database
(source db) and inserting locally (target db) using FOR EXECUTE
STATEMENT ON EXTERNAL with a select statement (likely the best
performance), or selecting locally (source db) with FOR SELECT and
inserting remotely (target db) using EXECUTE STATEMENT ON EXTERNAL with
an insert statement.

In both cases you have to define local variables for the column values.

You cannot do this in a single statement like INSERT ... SELECT, because
executing that with EXECUTE STATEMENT ON EXTERNAL would perform both the
select and the inserts on the remote database, and EXECUTE STATEMENT ON
EXTERNAL is the only way to do remote statement execution.

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Thank you very much for your clarification Mark!

Best Regards,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/eeb35062-569c-3e9b-a296-6ebdb1cc4588%40lawinegevaar.nl.