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?