Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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.