Cannot add new records into a table created like another.

[QuartoDie]

Using a split db, HSQL 2.3, I want to use sql to duplicate a table stucture and enter data in the new table. I know that I can easily copy a table or duplicate a table using the GUI interface simply by pulling on it with the mouse changing its name and copying either structure or structure and data, but I want to repeat this process several times and eliminate time consuming steps and simplify them for a another user. Thus I hope to include sql which will duplicate a table inserted a macro, and the user can press a button and run the macro depending on the table he wants to duplicate. NB. I am not worried about inserting the sql in the macro, it is just the sql I need.

Now I have tried the commands below (in the remark above each command you see that I noted that I cannot enter new data or add new records once the new table is opened):

—Creates same table format but no data can be entered as I would have been able to by using the GUI.

CREATE TABLE “mytable7” (Like “mytable3” including defaults excluding identity)

–as subquery merely saves a query cannot add new data either way as I would have been able to by using the GUI.

CREATE TABLE “mytable8” AS (SELECT * FROM “mytable1”) with data

So I am looking for a way to create new table based on a certain format, through sql without using the GUI. Is it possible? and why are these LIKE and (SELECT * FROM …) commands rendering “infertile” tables? Is it possible to change this?

[Ratslinger]

Whether using HSQL 1.8 embedded or split 2.3, when copying a table using SQL isn’t an exact copy. Two thing already noticed missing was 'No primary key" set and “No auto-increment” set. It appears you problem is caused by the absence of a primary key. Create another SQL statement to add the primary key:

ALTER TABLE NEWLY_CREATED_TABLE_NAME ADD PRIMARY KEY (YOUR_PK_FIELD)

After this you should be able to add records to the table.

[QuartoDie]

Thank you for the information and clarification.

[peterwt]

There are problems when creating a copy of a Table, either structure or structure and data. Assuming using a split database then as you say you can use the GUI to copy and paste the Table under a new name. It appears to work OK, but if you have an auto-incrementing field the auto-increment property is not copied. You need to use SQL

ALTER TABLE "copiedtable name" ALTER COLUMN "auto-incrementfieldname" GENERATED BY DEFAULT AS IDENTITY

If you have copied the data, the auto-increment field will pick up correctly from the last value.

There are further problems if you use SQL to copy the Table using

CREATE TABLE "mytable8" AS (SELECT * FROM "mytable1") WITH DATA

As @Ratslinger said the primary key and any auto-increment property is not set. You need to use SQL

ALTER TABLE "mytable8" ALTER COLUMN "auto-incrementfield" GENERATED BY DEFAULT AS IDENTITY
ALTER TABLE "mytable8" ADD PRIMARY KEY ("primarykeyfield")

The SQL must be in this order. If no auto-increment field is used omit the first line. Close and reopen the database after the SQL.

There are further problems. If the original Table has required entry or default values these properties are also not copied. You would need to use SQL to set these properties.

Bearing this in mind it would be difficult to create a universal macro to copy any Table.