Export CREATE TABLE ... to .gpkg

[Francesco Pelullo]

Hi everyone,

I connected Spatialite_gui to a .gpkg file with multiple tables.

I make queries to these tables (particularly a CREATE TABLE "TEMP" AS SELECT ... ).

Now TEMP is listed in the tables tree.

Is there a SQL command for make the TEMP table permanent in the .gpkg file?

In other words, how can I export new tables to the  linked .gpkg?

Thank you

Ciao

--fp

[a.fu...@lqt.it]

On Tue, 5 Jul 2022 16:16:52 +0200, Francesco Pelullo wrote:
> Hi everyone,
>
> I connected Spatialite_gui to a .gpkg file with multiple tables.
>
> I make queries to these tables (particularly a CREATE TABLE "TEMP" AS
> SELECT ... ).
>
> Now TEMP is listed in the tables tree.
>

ciao Francesco,

there is a serious error in the SQL snippet above.
it will not create a TEMPORY TABLE but a normal permanent
table oddly named "TEMP".

counter-check: close the DB connection and then reopen the
same DB-file. The "TEMP" table will still the there.

The correct syntax for creating a TEMPORARY TABLE is:

CREATE TEMPORARY TABLE somename AS SELECT ...

> Is there a SQL command for make the TEMP table permanent in the .gpkg
> file?
>

From a purely SQL point of view GPKG tables are simply tables.

What makes GPKG tables "special" is that they are registered
in the "metadata" table "gpkg_geometry_columns", something
you can do in the safest way by calling gpkgAddGeometryColumn()

putting all together:

1. first you must create the destination table by
omitting to specify the Geometry Column.

2. then you must add the Geometry Column by calling
gpkgAddGeometryColumn(); this will automatically
register all "metadata" required by the GPKS standard.

3. finally you can populate the new table, possibly
using an SQL statement like

INSERT INTO xxxx SELECT ....

and this is all; you just added an additional
permanent GPKG table in your DB

bye Sandro