Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

H2 database, insert by selecting results from CSVREAD

Tags:

sql

csv

h2

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?

like image 337
kaskelotti Avatar asked Oct 10 '13 15:10

kaskelotti


Video Answer


1 Answers

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 ...
like image 120
Thomas Mueller Avatar answered Sep 20 '22 04:09

Thomas Mueller