Select into cached -- does it create a permanent table?

[trueriver]

I want to create a number of normailised tables from a temporary table containing rows imported from a spreadsheet. I have tweaked the columns to the appropriate formats, but there is a lot of redundancy so I want to normailse to 3NF,

The syntax I am used to (which I believed was ANSI standard) is

CREATE TABLE FOO AS SELECT …

but the base embedded engine does not accept anything other than column defs after CREATE TABLE.

I want to create the table and fill it with data at the same time. Having done that, I want to be able to delete the temporary table and keep the new, normailised, tables.

Here is the exact query I wrote originally:

CREATE TABLE “logins” AS
(
SELECT “xx”.“PK”, “xx”.“email”, “yy”.“PK” AS “refbyFK”, “xx”.“fnam”, “xx”.“snam”, “xx”.“dob”, “xx”.“addr”, “xx”.“prev”, “xx”.“comment” FROM “pasted_dates” AS “xx”, “pasted_dates” AS “yy” WHERE “xx”.“recby” = “yy”.“email”
)

I have found the syntax

SELECT … INTO CACHED FOO

will this have the desired effect? Or will it create some kind of materialised View in which the rows will all disappear when the input rows are later dropped?

In any case, I cannot get that to work either. I tried

SELECT
into CACHED “logins”

“xx”.“PK”, “xx”.“email”, “yy”.“PK” AS “refbyFK”, “xx”.“fnam”, “xx”.“snam”, “xx”.“dob”, “xx”.“addr”, “xx”.“prev”, “xx”.“comment” FROM “pasted_dates” AS “xx”, “pasted_dates” AS “yy” WHERE “xx”.“recby” = “yy”.“email”

and I tried the same again with the INTO clause at the end.

[PYS]

Hi

I apologize in advance if I understand something wrong (language problem?) But for me the answer to the question:

I have found the syntax SELECT … INTO CACHED FOO will this have the desired effect? Or will it create some kind of materialised View… ?

The answer is yes, you can use this syntax.

A new table is created (however it will be necessary to refresh the list of tables: View▸Refresh Tables). It is not a view.

The limit is that the table is the result of a SELECT with all the induced limits: no primary key creation, no recovery of all the attributes (default values …). You can of course modify later if necessary (ALTER)

So, with Toos▸SQLyou can execute something like:

SELECT "Name", "LastName"  INTO  CACHED "New" FROM "Clients" 

Regards

[trueriver]

Ahaa!!

Thank you. I had the INTO clause in the wrong place (tried several options but not the right one.)

And yes, am anticipating having to ALTER TABLE … ADD INDEX etc afterwards.

The following query worked for me, with or without the CACHED keyword.

SELECT “xx”.“PK”, “xx”.“email”, “yy”.“PK” AS “refbyFK”, “xx”.“fnam”, “xx”.“snam”, “xx”.“dob”, “xx”.“addr”, “xx”.“prev”, “xx”.“comment”

INTO “logins”

FROM “pasted_dates” AS “xx”, “pasted_dates” AS “yy”

WHERE “xx”.“recby” = “yy”.“email”

[trueriver]

(apols for this double post, a comment I added earlier seemed to have disappeared but then came back. Browser cache, no doubt…)

[Ratslinger]

Hello,

HSQLDB v1.8 embedded is very old. This version does not allow creation of a new table based upon another table. Just tested v3.4 in a split mode and it does this with the following syntax:

CREATE TABLE "TestCopy" AS
    (SELECT "id_bouquet", "bouquet_Name"
        FROM "T_bouquets") WITH DATA

Table was created with the appropriate data.

With v1.8 it appears creation, data copy and deletion will each be separate processes.

Please realize, the current default database is being changed to Firebird 3.0 in LO v6.1 (soon to be released and currently available using Enable experimental features). There are a few problems with converting & copying data (numeric & decimal fields are current problem) but those problems are being addressed. Firebird embedded itself does work once you have the data in place. Have had it up and running for a few months now using thousands of records containing a variety of data (including numeric & decimal). This was a converted MySQL DB dealing with personal finances.

Long term, consider Firebird.

[trueriver]

Thanks - the issue of which version of HSQLDB had not occurred to me, derrrrrr, and I now realise that is so important to check version as well as the name of the engine

Edit:
however it turns out that this syntax does work on the version of HSQLDB embedded in LO 5.2.7, which is what my Debian reopos give me.

The advice to check what version of the embedded engine is being used is still good advice tho.

[trueriver]

I got it working eventually - had the INTO clause in the wrong place, despite trying several guesses.

SELECT … INTO … FROM … WHERE

And each “…” gives relevant parameters for the preceding keyword

This version of my query worked for me, and also worked without the CACHED keyword.

SELECT “xx”.“PK”, “xx”.“email”, “yy”.“PK” AS “refbyFK”, “xx”.“fnam”, “xx”.“snam”, “xx”.“dob”, “xx”.“addr”, “xx”.“prev”, “xx”.“comment”

INTO CACHED “logins”

FROM “pasted_dates” AS “xx”, “pasted_dates” AS “yy”

WHERE “xx”.“recby” = “yy”.“email”