H2 database, insert by selecting results from CSVREAD

Question

I have a CSV file like

1,hello,13
2,world,14
3,ciao,26

I'm trying to use CSVREAD function to read this file into database, like this

insert into my_table( id, message, code ) values (
  select convert( "id",bigint ), "message", convert( "code", bigint)
  from CSVREAD( 'myfile.csv', 'id,message,code', null )
);

For some reason I keep on getting SQL error stating that the column count does not match.

The table is created with Hibernate/GORM and contains the fields I try to insert into.

The select itself seems to work, or at least it does not cause any errors when executed alone. What's wrong with my statement?

Answer 1

You have used

insert into my_table(...) values (select ...)

but you should use, as documented in the SQL railroad diagrams,

insert into my_table(...) select ...

Actually, for H2, it is a bit faster if you create the table as follows, but I understand it is not always possible:

create table my_table(...) as select ...