H2数据库，通过从CSVREAD中选择结果来插入 [英] H2 database, insert by selecting results from CSVREAD

问题描述

我有一个CSV文件，例如

I have a CSV file like

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

我正在尝试使用CSVREAD函数将文件读入数据库，就像这样

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 )
);

出于某种原因，我继续获得SQL error stating that the column count does not match.

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

该表是使用Hibernate/GORM创建的，其中包含我尝试插入的字段.

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

select本身似乎起作用，或者至少单独执行时不会引起任何错误. 我的陈述出了什么问题?

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

推荐答案

您已使用

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

，但是您应该使用 SQL铁路图中记录的

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

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

实际上，对于H2，如果按如下方式创建表，则速度会更快，但我了解并非总是可能的:

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 ...

这篇关于H2数据库，通过从CSVREAD中选择结果来插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！