SELECT INTO with HSQLDB

Question

I am trying to create a new table from the result of a select. This works fine with SQL Server:

SELECT * INTO newTable FROM (SELECT col1, col2, col3 FROM oldTable) x;

Now, I want to achieve the exact same thing with HSQLDB (Version 2.2). I have tried several forms like

SELECT * INTO newTable FROM (SELECT col1, col2, col3 FROM oldTable);
SELECT INTO newTable FROM SELECT col1, col2, col3 FROM oldTable;
CREATE TABLE newTable AS SELECT col1, col2, col3 FROM oldTable;

All these variants result in some form of syntax error. How can I create a table from a select with HSQLDB?

Answer 1

The manual has an example for this:

CREATE TABLE t (a, b, c) AS (SELECT * FROM atable) WITH DATA

HSQLDB requires parentheses around the select (unlike all other DBMS) and it also requires the WITH DATA clause