HSQLDB将文本表文件导入到传统表中 [英] HSQLDB importing a text table file into a traditional table

问题描述

我有两个相同的表结构

1. 缓存表（普通表）


2. 文本表（textfiletable）

我想将数据从表[2]复制到表[1]

我正在使用此插入语法

INSERT INTO normaltable ("COL1", "COL2", "COL3")
SELECT COL1, COL2, COL3
FROM textfiletable;

我收到此错误

data exception:string data, right truncation/Error code: -3401/state: 22001

使用其他插入语法时

SELECT COL1, COL2, COL3
INTO normaltable
FROM textfiletable;

我收到此错误

unexpected token : INTO required: FROM:line:2/error code: -5581/state:42581

有人可以解释吗？

我会更新脚本的详细信息，以供您参考：

im update the detail of my script for you to refer:

drop table pis_mdc;
drop table normaltable;
drop table textfiletable;

--- original table
CREATE TABLE PIS_MDC
(UD_MDC_CODE VARCHAR(25) ,
DRUG_GNR_NAME VARCHAR(200)DEFAULT 'NULL',
DRUG_PRODUCT_NAME VARCHAR(100),
UD_MDC_DESC VARCHAR(200)DEFAULT 'NULL',
ACTIVE_INGREDIENT_CODE VARCHAR(600),
DEF_CAUTIONARY_CODE VARCHAR(200),
DOSAGE_FORM_CODE VARCHAR(100)DEFAULT 'NULL',
DEF_DOSAGE VARCHAR(20),
DEF_ROUTE_CODE VARCHAR(100)DEFAULT 'NULL',
DEF_ADVISORY_CODE VARCHAR(200)DEFAULT 'NULL',
UD_ATC_CODE VARCHAR(15) ,
STATUS VARCHAR(20),
DRUG_STRENGTH VARCHAR(20),
PRIMARY KEY(UD_MDC_CODE,UD_ATC_CODE));

--create cached tbl
create cached table normaltable
(UD_MDC_CODE VARCHAR(25) ,
DRUG_GNR_NAME VARCHAR(100)DEFAULT 'NULL',
DRUG_PRODUCT_NAME VARCHAR(200),
UD_MDC_DESC VARCHAR(200)DEFAULT 'NULL',
ACTIVE_INGREDIENT_CODE  VARCHAR(600),
DEF_CAUTIONARY_CODE VARCHAR(200),
DOSAGE_FORM_CODE VARCHAR(100)DEFAULT 'NULL',
DEF_DOSAGE VARCHAR (20),
DEF_ROUTE_CODE VARCHAR(100)DEFAULT 'NULL',
DEF_ADVISORY_CODE VARCHAR(200)DEFAULT 'NULL',
UD_ATC_CODE VARCHAR(15) ,
STATUS VARCHAR(20),
DRUG_STRENGTH VARCHAR(20));

--create the table that table
create text table textfiletable 
(UD_MDC_CODE VARCHAR(25) ,
DRUG_GNR_NAME VARCHAR(100)DEFAULT 'NULL',
DRUG_PRODUCT_NAME VARCHAR(200),
UD_MDC_DESC VARCHAR(200)DEFAULT 'NULL',
ACTIVE_INGREDIENT_CODE VARCHAR(600),
DEF_CAUTIONARY_CODE VARCHAR(200),
DOSAGE_FORM_CODE VARCHAR(100)DEFAULT 'NULL',
DEF_DOSAGE VARCHAR (20),
DEF_ROUTE_CODE VARCHAR(100)DEFAULT 'NULL',
DEF_ADVISORY_CODE VARCHAR(200)DEFAULT 'NULL',
UD_ATC_CODE VARCHAR(15) ,
STATUS VARCHAR(20),
DRUG_STRENGTH VARCHAR(20));


--SET TABLE textfiletable SOURCE ON

SET TABLE textfiletable SOURCE "gabung.csv;ignore_first=true"

--OPTION 0 line 1 with quotes without MAX(CHAR_LENGTH())
INSERT INTO normaltable ("UD_MDC_CODE","DRUG_GNR_NAME","DRUG_PRODUCT_NAME","UD_MDC_DESC","ACTIVE_INGREDIENT_CODE","DEF_CAUTIONARY_CODE","DOSAGE_FORM_CODE","DEF_DOSAGE","DEF_ROUTE_CODE","DEF_ADVISORY_CODE","UD_ATC_CODE","STATUS","DRUG_STRENGTH") 
select UD_MDC_CODE,DRUG_GNR_NAME,DRUG_PRODUCT_NAME,UD_MDC_DESC,ACTIVE_INGREDIENT_CODE,DEF_CAUTIONARY_CODE,DOSAGE_FORM_CODE,DEF_DOSAGE,DEF_ROUTE_CODE,DEF_ADVISORY_CODE,UD_ATC_CODE,STATUS,DRUG_STRENGTH 
from textfiletable;

-- OPTION 1 line 1 without quotes
--SELECT MAX(CHAR_LENGTH(COL1)), MAX(CHAR_LENGTH(COL2)), ... FROM textfiletable 
--INSERT INTO normaltable ("UD_MDC_CODE","DRUG_GNR_NAME","DRUG_PRODUCT_NAME","UD_MDC_DESC","ACTIVE_INGREDIENT_CODE","DEF_CAUTIONARY_CODE","DOSAGE_FORM_CODE","DEF_DOSAGE","DEF_ROUTE_CODE","DEF_ADVISORY_CODE","UD_ATC_CODE","STATUS","DRUG_STRENGTH")
INSERT INTO normaltable (UD_MDC_CODE,DRUG_GNR_NAME,DRUG_PRODUCT_NAME,UD_MDC_DESC,ACTIVE_INGREDIENT_CODE,DEF_CAUTIONARY_CODE,DOSAGE_FORM_CODE,DEF_DOSAGE,DEF_ROUTE_CODE,DEF_ADVISORY_CODE,UD_ATC_CODE,STATUS,DRUG_STRENGTH)
SELECT MAX(CHAR_LENGTH(UD_MDC_CODE)),MAX(CHAR_LENGTH(DRUG_GNR_NAME)),MAX(CHAR_LENGTH(DRUG_PRODUCT_NAME)),MAX(CHAR_LENGTH(UD_MDC_DESC)),MAX(CHAR_LENGTH(ACTIVE_INGREDIENT_CODE)),MAX(CHAR_LENGTH(DEF_CAUTIONARY_CODE)),MAX(CHAR_LENGTH(DOSAGE_FORM_CODE)),MAX(CHAR_LENGTH(DEF_DOSAGE)),MAX(CHAR_LENGTH(DEF_ROUTE_CODE)),MAX(CHAR_LENGTH(DEF_ADVISORY_CODE)),MAX(CHAR_LENGTH(UD_ATC_CODE)),MAX(CHAR_LENGTH(STATUS)),MAX(CHAR_LENGTH(DRUG_STRENGTH))
FROM textfiletable
--ORDER BY UD_MDC_CODE DESC;

--we are done with the text file table
DROP TABLE  textfiletable;
COMMIT;

textfiletable中要复制/插入到normaltable
https://docs.google.com/open?id=0B-7fkDVcLbxnclBZZDZRQWg4ZEE

example list of data from textfiletable that want to be copy/insert into normaltable https://docs.google.com/open?id=0B-7fkDVcLbxnclBZZDZRQWg4ZEE

使用max（char_length（col_name））的结果示例
https://docs.google.com/open?id=0B-7fkDVcLbxnREpzNUp3d3NNSlE

example of result of using max(char_length(col_name)) https://docs.google.com/open?id=0B-7fkDVcLbxnREpzNUp3d3NNSlE

推荐答案

意外令牌错误是语法错误。这表示版本2.x不支持此语法。

The unexpected token error is a syntax error. It means this syntax is not supported in version 2.x.

数据异常错误表示某些字符串存储在TEXT表太大，无法插入CACHED表的字段中。尝试查询，例如：

The data exception error means some string stored in the TEXT table is too large to insert into the field in the CACHED table. Try a query such as:

SELECT MAX(CHAR_LENGTH(UD_MDC_CODE)), MAX(CHAR_LENGTH(DRUG_GNR_NAME)), MAX(CHAR_LENGTH(DRUG_PRODUCT_NAME)) FROM textfiletable 

查看结果并将最大长度与列的大小进行比较 normaltable 。例如，如果UD_MDC_CODE的MAX值为29，则必须将该列增大。可以使用以下语句将列的大小更改为30：

look at the result and compare the maximum lengths with the column size of the normaltable. For example, if the MAX value for UD_MDC_CODE is 29, then you have to make this column larger. This can be done using this statement which changes the size of the column to 30:

ALTER TABLE normaltable ALTER COLUMN UD_MDC_CODE SET DATA TYPE VARCHAR(30)

仅对大小小于MAX值的列使用ALTER TABLE语句。对所有太小的列完成此操作后，请使用INSERT INTO语句将数据从TEXT表复制到CACHED表。此处记录了ALTER TABLE语句：

Use the ALTER TABLE statement only for columns that have a smaller size than the MAX value. When you have done this for all the columns that are too samll, use your INSERT INTO statement to copy the data from the TEXT table to the CACHED table. The ALTER TABLE statement is documented here:

http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_table_manupulation

这篇关于HSQLDB将文本表文件导入到传统表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！