[Solved] Multiple insert statement

[Tshep]

Hello: Instead of repeated insert statements to add data to a table, I want to do something like this:

Code: Select all

insert into tablename(field1,field2,field3) values ('value1','value2','value3'),('valuex','valuey','valuez'),('valuen','valuem','valueo')

, which is easy to do in Mysql or Sqlserver. But Openoffice flags a syntax error. Is this sort of multiple insert statement legal in openoffice? If not, is there any other way to do a multiple insert?

[UnklDonald418]

The version of HSQLDB database engine (1.8) that is used in an Embedded database is quite old and doesn't allow insert statements like that so the only solution is a different insert statement for each record.

Using a JDBC connection (Split Database) with a more up to date version of HSQLDB allows you to insert multiple records with the sort of syntax you gave.
With any version of HSQLDB any table or field name containing lower case letters must be enclosed in double quotes.

[Villeroy]

Create a view that selects the values to be inserted.
Copy the view icon.
Select the icon of the target table and paste.
Choose "Append Data" and ensure that the right table name is given.
Hit the [Next] button and map the source columns to the target columns.
If the order of view columns matches the order and types of the target columns, you can confirm the dialog.

You can also copy a range of cells from a spreadsheet and paste to the icon of the target table.

[Villeroy]

This works for me with embedded HSQL 1.8

Code: Select all

insert into "TARGET" (SELECT NULL, "PATNO","DATE","AMOUNT" FROM "SOURCE" WHERE YEAR("DATUM")=2012)

The first column of TARGET is an auto-ID and it is filled with NULL. The subsequent fields "PATNO","DATE","AMOUNT" from the "SOURCE" table have corresponding fields at positions 2,3 and 4 in the target table. The WHERE clause is just an arbitrary filter criterion.

This one works as well. It inserts records from source to target where the PK (primary key) does not exist in target.

Code: Select all

INSERT INTO "TARGET" (SELECT NULL, "PATNO","DATE","AMOUNT" FROM "SOURCE" WHERE "SOURCE"."PK" NOT IN (SELECT"PK" FROM "TARGET"))