Databases
StackOverflow2013
Postsdbo
POHSQLDB importing a text table file into a traditional table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHSQLDB importing a text table file into a traditional table
    primarykey
    data
    text
    <p>i have two the same table structure</p> <ol> <li>cache table (normaltable)</li> <li>text table (textfiletable)</li> </ol> <p><strong>i want to copy data from table [2] into table [1]</strong></p> <p>i'm using this insert syntax </p> <pre><code>INSERT INTO normaltable ("COL1", "COL2", "COL3") SELECT COL1, COL2, COL3 FROM textfiletable; </code></pre> <p>i get this error</p> <pre><code>data exception:string data, right truncation/Error code: -3401/state: 22001 </code></pre> <p>when using another insert syntax</p> <pre><code>SELECT COL1, COL2, COL3 INTO normaltable FROM textfiletable; </code></pre> <p>i get this error</p> <pre><code>unexpected token : INTO required: FROM:line:2/error code: -5581/state:42581 </code></pre> <p>can anybody explain?</p> <hr> <p>im update the detail of my script for you to refer:</p> <pre><code>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; </code></pre> <p>example list of data from textfiletable that want to be copy/insert into normaltable <a href="https://docs.google.com/open?id=0B-7fkDVcLbxnclBZZDZRQWg4ZEE" rel="nofollow">https://docs.google.com/open?id=0B-7fkDVcLbxnclBZZDZRQWg4ZEE</a></p> <p>example of result of using max(char_length(col_name)) <a href="https://docs.google.com/open?id=0B-7fkDVcLbxnREpzNUp3d3NNSlE" rel="nofollow">https://docs.google.com/open?id=0B-7fkDVcLbxnREpzNUp3d3NNSlE</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USshatilah zakaria
    1. USshatilah zakaria
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload