[Solved] Insert Into SQL from Query gives Table Not Found

[evwool]

Please could you check if my syntax is correct for this Insert Into SQL. I'm entering it using Tools, Sql and the cases for the table and column names match the real tables and columns.
The data type is the same in Qry1's underlying table and TblEmail3Split
I don't know if you can use a Query in INSERT INTO sql.

INSERT INTO "TblEmail3Split" ("BefAt", "AftAt", "SorID") SELECT "T1", "T2", "SorID" FROM "Qry1";

When I run this query I get the error
1: Table not found in statement [INSERT INTO "TblEmail3Split" ("BefAt", "AftAt", "SorID") SELECT "T1", "T2", "SorID" FROM "Qry1"]

[eremmel]

A quick test shows indeed that you can not use a query as 'table' in the select statement of an insert statement with HSQLDB

[Villeroy]

But a view should work since views are in the scope of the HSQL backend.

[evwool]

Ouch! Thanks for the replies. If queries can't be used for Insert Into statements, that is quite a lack. Is there a workround I can use? I need to filter the data before I append it to the table.

[Villeroy]

You are working with 2 applications at the same time.
The first one is a database server (backend). That could be MySQL, SQLite, Oracle, MS SQL. If we are talking about one of the "integrated databases", that database application is HSQLDB as documented on http://hsqldb.org/doc/guide/ch09.html
When using the command line tool Tools>SQL... then you talk directly to that program. Theoretically, applications other than OOo Base may talk to the same database backend.

The second appliciation is OpenOffice.org which allows to connect with several types of databases. Once the connection is established you can access the database tables and use OOo as a so called frontend application.
Then you can add additional, OOo-specific queries, forms and reports. These elements are not part of the database. They are part of the front-end. The front-end is not the database. It is a set of tools to do something with the database.
The "normal queries" are OOo specific. They can be edited in a graphical designer and in the SQL view of the query designer. Theoretically, all queries follow the same rules of syntax regardless of the type of backend. While in SQL view you can switch to "direct SQL mode" which again talks to the database directly so you may use some features that are supported by the database but not Base.

HSQLDB supports views, which can be seen as queries that are stored in the database itself. Views are exposed to any frontend application that connects to the database.
Even if the "integrated Base database" can not interact with any frontend other than Base (so the views are a little bit academic), it behaves exactly like any external database, and in fact it is an external one since your database gets extracted from the "database document" every time you "open" the odb file. Then a HSQLDB server connects the frontend tools to the extracted database, very similar to an external database.

[evwool]

I don't know what you mean, Villeroy. I've only ever heard about Front End and Back End to do with having databases on a server - the tables are the backend and the forms are the front end on the user's machine. Are you saying that there is a way I can filter a table in the database (it's an imported table) and append it to another table in the database?

[Villeroy]

Look at the status bar. There you find some string like "Embedded database HSQLDB". http://hsqldb.org is a server based database. Your Base front end is connected with an external database as soon as you "opened" the odb and the table data have been extracted to a temporary directory.

You can import from one database to another one using the clipboard or drag&drop interface. Simply drag the table onto the other table or copy and paste (a spreadsheet range with column labels works as well). A wizard pops up where you choose to append data to the respective table and map each imported field to a field in the target table.
If the target table has a unique index on some fields and the pasted rows have duplicates conflicting with the index, you'll get a warning. If you continue the import, the duplicates are skipped.
You can also generate a new table from clipboard and then INSERT from one table to the other and I believe, you can use a view (backend query) to filter the rows.

[evwool]

Oh, I see what you mean! If I drag my filtered query onto the Tables icon in the panel on the left of the database window, I get the option to Append the data. I just type the name of the table which I want to append the data to, a window opens letting me match which fields I want to append to, and the data is automatically appended. No need to write SQL or anything. Thanks everyone. I'll take this as solved.