OpenOffice Base - Merge >200 tables into one

Question

I am new to OpenOffice Base. In a couple of hours I will have about 290 tables with identical headers. What is the quickest and easiest way to merge all these into one (1) single table?

Answer 1

Go to Tools -> SQL and use the CREATE TABLE AS syntax. A command like this one can quickly be created for all 290 tables using a decent text editor such as Vim.

create table Table3 as
select * from Table1
union select * from Table2
union select * from Table3
union select * from Table4;

To see the new table, go to View -> Refresh Tables.

This example was tested with MySQL. The syntax may be different depending on the database engine.

EDIT:

The command above does not work for HSQLDB 1.8, so you must be using a different engine. How did you set up the connection?

Anyway, for all available table names in MySQL, see Get table names using SELECT statement in MySQL. For HSQLDB see How to see all the tables in an HSQLDB database?