SQLite Forum

inconsistent sqlite3 shell
Login

inconsistent sqlite3 shell

(1) By Roman (moskvich412) on 2020-12-29 23:10:43 [link] [source]

Dear SQLiters,

I have a rather long SQL statement and I am using sqlite3 shell to insert data. I tried old version 3.16 and new 3.34 , both show similar issue. As far as I can tell, the length of SQL statement is well bellow various limits.

I can not come up a summary, below is example (very long). I have two databases with examData table and I am trying to unite them. The logic is simple:

ATTACH DATABASE 'second.sqlite' AS secondDB; INSERT INTO main.examData(..) SELECT ... FROM secondDB.examData;

When I try, on repeated attempts I get various errors, that number of columns in the target is not the same as in the SELECT. Or that columns do not exit when they do. And these errors (including column names) vary from attempt to attempt. If I try long enough, INSERT eventually succeeds. The errors are "syntax", not constrains on values.

First schema for the table, then INSERT.

CREATE TABLE examData ( subjectID INTEGER PRIMARY KEY NOT NULL, ICV REAL , Left_Cerebral_White_Matter REAL , Left_Cerebral_Cortex REAL , Left_Lateral_Ventricle REAL , Left_Inf_Lat_Vent REAL , Left_Cerebellum_White_Matter REAL , Left_Cerebellum_Cortex REAL , Left_Thalamus_Proper REAL , Left_Caudate REAL , Left_Putamen REAL , Left_Pallidum REAL , Third_Ventricle REAL , Fourth_Ventricle REAL , Brain_Stem REAL , Left_Hippocampus REAL , Left_Amygdala REAL , CSF REAL , Left_Accumbens_area REAL , Left_VentralDC REAL , Left_choroid_plexus REAL , Right_Cerebral_White_Matter REAL , Right_Cerebral_Cortex REAL , Right_Lateral_Ventricle REAL , Right_Inf_Lat_Vent REAL , Right_Cerebellum_White_Matter REAL , Right_Cerebellum_Cortex REAL , Right_Thalamus_Proper REAL , Right_Caudate REAL , Right_Putamen REAL , Right_Pallidum REAL , Right_Hippocampus REAL , Right_Amygdala REAL , Right_Accumbens_area REAL , Right_VentralDC REAL , Right_choroid_plexus REAL , WM_hypointensities REAL , non_WM_hypointensities REAL , Optic_Chiasm REAL , CC_Posterior REAL , CC_Mid_Posterior REAL , CC_Central REAL , CC_Mid_Anterior REAL , CC_Anterior REAL , Left_UnsegmentedWhiteMatter REAL , Right_UnsegmentedWhiteMatter REAL , ctx_lh_bankssts REAL , ctx_lh_caudalanteriorcingulate REAL , ctx_lh_caudalmiddlefrontal REAL , ctx_lh_cuneus REAL , ctx_lh_entorhinal REAL , ctx_lh_frontalpole REAL , ctx_lh_fusiform REAL , ctx_lh_inferiorparietal REAL , ctx_lh_inferiortemporal REAL , ctx_lh_insula REAL , ctx_lh_isthmuscingulate REAL , ctx_lh_lateraloccipital REAL , ctx_lh_lateralorbitofrontal REAL , ctx_lh_lingual REAL , ctx_lh_medialorbitofrontal REAL , ctx_lh_middletemporal REAL , ctx_lh_paracentral REAL , ctx_lh_parahippocampal REAL , ctx_lh_parsopercularis REAL , ctx_lh_parsorbitalis REAL , ctx_lh_parstriangularis REAL , ctx_lh_pericalcarine REAL , ctx_lh_postcentral REAL , ctx_lh_posteriorcingulate REAL , ctx_lh_precentral REAL , ctx_lh_precuneus REAL , ctx_lh_rostralanteriorcingulate REAL , ctx_lh_rostralmiddlefrontal REAL , ctx_lh_superiorfrontal REAL , ctx_lh_superiorparietal REAL , ctx_lh_superiortemporal REAL , ctx_lh_supramarginal REAL , ctx_lh_temporalpole REAL , ctx_lh_transversetemporal REAL , ctx_lh_unknown REAL , ctx_rh_bankssts REAL , ctx_rh_caudalanteriorcingulate REAL , ctx_rh_caudalmiddlefrontal REAL , ctx_rh_cuneus REAL , ctx_rh_entorhinal REAL , ctx_rh_frontalpole REAL , ctx_rh_fusiform REAL , ctx_rh_inferiorparietal REAL , ctx_rh_inferiortemporal REAL , ctx_rh_insula REAL , ctx_rh_isthmuscingulate REAL , ctx_rh_lateraloccipital REAL , ctx_rh_lateralorbitofrontal REAL , ctx_rh_lingual REAL , ctx_rh_medialorbitofrontal REAL , ctx_rh_middletemporal REAL , ctx_rh_paracentral REAL , ctx_rh_parahippocampal REAL , ctx_rh_parsopercularis REAL , ctx_rh_parsorbitalis REAL , ctx_rh_parstriangularis REAL , ctx_rh_pericalcarine REAL , ctx_rh_postcentral REAL , ctx_rh_posteriorcingulate REAL , ctx_rh_precentral REAL , ctx_rh_precuneus REAL , ctx_rh_rostralanteriorcingulate REAL , ctx_rh_rostralmiddlefrontal REAL , ctx_rh_superiorfrontal REAL , ctx_rh_superiorparietal REAL , ctx_rh_superiortemporal REAL , ctx_rh_supramarginal REAL , ctx_rh_temporalpole REAL , ctx_rh_transversetemporal REAL , ctx_rh_unknown REAL , wm_lh_bankssts REAL , wm_lh_caudalanteriorcingulate REAL , wm_lh_caudalmiddlefrontal REAL , wm_lh_cuneus REAL , wm_lh_entorhinal REAL , wm_lh_frontalpole REAL , wm_lh_fusiform REAL , wm_lh_inferiorparietal REAL , wm_lh_inferiortemporal REAL , wm_lh_insula REAL , wm_lh_isthmuscingulate REAL , wm_lh_lateraloccipital REAL , wm_lh_lateralorbitofrontal REAL , wm_lh_lingual REAL , wm_lh_medialorbitofrontal REAL , wm_lh_middletemporal REAL , wm_lh_paracentral REAL , wm_lh_parahippocampal REAL , wm_lh_parsopercularis REAL , wm_lh_parsorbitalis REAL , wm_lh_parstriangularis REAL , wm_lh_pericalcarine REAL , wm_lh_postcentral REAL , wm_lh_posteriorcingulate REAL , wm_lh_precentral REAL , wm_lh_precuneus REAL , wm_lh_rostralanteriorcingulate REAL , wm_lh_rostralmiddlefrontal REAL , wm_lh_superiorfrontal REAL , wm_lh_superiorparietal REAL , wm_lh_superiortemporal REAL , wm_lh_supramarginal REAL , wm_lh_temporalpole REAL , wm_lh_transversetemporal REAL , wm_rh_bankssts REAL , wm_rh_caudalanteriorcingulate REAL , wm_rh_caudalmiddlefrontal REAL , wm_rh_cuneus REAL , wm_rh_entorhinal REAL , wm_rh_frontalpole REAL , wm_rh_fusiform REAL , wm_rh_inferiorparietal REAL , wm_rh_inferiortemporal REAL , wm_rh_insula REAL , wm_rh_isthmuscingulate REAL , wm_rh_lateraloccipital REAL , wm_rh_lateralorbitofrontal REAL , wm_rh_lingual REAL , wm_rh_medialorbitofrontal REAL , wm_rh_middletemporal REAL , wm_rh_paracentral REAL , wm_rh_parahippocampal REAL , wm_rh_parsopercularis REAL , wm_rh_parsorbitalis REAL , wm_rh_parstriangularis REAL , wm_rh_pericalcarine REAL , wm_rh_postcentral REAL , wm_rh_posteriorcingulate REAL , wm_rh_precentral REAL , wm_rh_precuneus REAL , wm_rh_rostralanteriorcingulate REAL , wm_rh_rostralmiddlefrontal REAL , wm_rh_superiorfrontal REAL , wm_rh_superiorparietal REAL , wm_rh_superiortemporal REAL , wm_rh_supramarginal REAL , wm_rh_temporalpole REAL , wm_rh_transversetemporal REAL);

INSERT OR IGNORE INTO main.examData( subjectID , ICV , Left_Cerebral_White_Matter , Left_Cerebral_Cortex , Left_Lateral_Ventricle , Left_Inf_Lat_Vent , Left_Cerebellum_White_Matter , Left_Cerebellum_Cortex , Left_Thalamus_Proper , Left_Caudate , Left_Putamen , Left_Pallidum , Third_Ventricle , Fourth_Ventricle , Brain_Stem , Left_Hippocampus , Left_Amygdala , CSF , Left_Accumbens_area , Left_VentralDC , Left_choroid_plexus , Right_Cerebral_White_Matter , Right_Cerebral_Cortex , Right_Lateral_Ventricle , Right_Inf_Lat_Vent , Right_Cerebellum_White_Matter , Right_Cerebellum_Cortex , Right_Thalamus_Proper , Right_Caudate , Right_Putamen , Right_Pallidum , Right_Hippocampus , Right_Amygdala , Right_Accumbens_area , Right_VentralDC , Right_choroid_plexus , WM_hypointensities , non_WM_hypointensities , Optic_Chiasm , CC_Posterior , CC_Mid_Posterior , CC_Central , CC_Mid_Anterior , CC_Anterior , Left_UnsegmentedWhiteMatter , Right_UnsegmentedWhiteMatter , ctx_lh_bankssts , ctx_lh_caudalanteriorcingulate , ctx_lh_caudalmiddlefrontal , ctx_lh_cuneus , ctx_lh_entorhinal , ctx_lh_frontalpole , ctx_lh_fusiform , ctx_lh_inferiorparietal , ctx_lh_inferiortemporal , ctx_lh_insula , ctx_lh_isthmuscingulate , ctx_lh_lateraloccipital , ctx_lh_lateralorbitofrontal , ctx_lh_lingual , ctx_lh_medialorbitofrontal , ctx_lh_middletemporal , ctx_lh_paracentral , ctx_lh_parahippocampal , ctx_lh_parsopercularis , ctx_lh_parsorbitalis , ctx_lh_parstriangularis , ctx_lh_pericalcarine , ctx_lh_postcentral , ctx_lh_posteriorcingulate , ctx_lh_precentral , ctx_lh_precuneus , ctx_lh_rostralanteriorcingulate , ctx_lh_rostralmiddlefrontal , ctx_lh_superiorfrontal , ctx_lh_superiorparietal , ctx_lh_superiortemporal , ctx_lh_supramarginal , ctx_lh_temporalpole , ctx_lh_transversetemporal , ctx_lh_unknown , ctx_rh_bankssts , ctx_rh_caudalanteriorcingulate , ctx_rh_caudalmiddlefrontal , ctx_rh_cuneus , ctx_rh_entorhinal , ctx_rh_frontalpole , ctx_rh_fusiform , ctx_rh_inferiorparietal , ctx_rh_inferiortemporal , ctx_rh_insula , ctx_rh_isthmuscingulate , ctx_rh_lateraloccipital , ctx_rh_lateralorbitofrontal , ctx_rh_lingual , ctx_rh_medialorbitofrontal , ctx_rh_middletemporal , ctx_rh_paracentral , ctx_rh_parahippocampal , ctx_rh_parsopercularis , ctx_rh_parsorbitalis , ctx_rh_parstriangularis , ctx_rh_pericalcarine , ctx_rh_postcentral , ctx_rh_posteriorcingulate , ctx_rh_precentral , ctx_rh_precuneus , ctx_rh_rostralanteriorcingulate , ctx_rh_rostralmiddlefrontal , ctx_rh_superiorfrontal , ctx_rh_superiorparietal , ctx_rh_superiortemporal , ctx_rh_supramarginal , ctx_rh_temporalpole , ctx_rh_transversetemporal , ctx_rh_unknown , wm_lh_bankssts , wm_lh_caudalanteriorcingulate , wm_lh_caudalmiddlefrontal , wm_lh_cuneus , wm_lh_entorhinal , wm_lh_frontalpole , wm_lh_fusiform , wm_lh_inferiorparietal , wm_lh_inferiortemporal , wm_lh_insula , wm_lh_isthmuscingulate , wm_lh_lateraloccipital , wm_lh_lateralorbitofrontal , wm_lh_lingual , wm_lh_medialorbitofrontal , wm_lh_middletemporal , wm_lh_paracentral , wm_lh_parahippocampal , wm_lh_parsopercularis , wm_lh_parsorbitalis , wm_lh_parstriangularis , wm_lh_pericalcarine , wm_lh_postcentral , wm_lh_posteriorcingulate , wm_lh_precentral , wm_lh_precuneus , wm_lh_rostralanteriorcingulate , wm_lh_rostralmiddlefrontal , wm_lh_superiorfrontal , wm_lh_superiorparietal , wm_lh_superiortemporal , wm_lh_supramarginal , wm_lh_temporalpole , wm_lh_transversetemporal , wm_rh_bankssts , wm_rh_caudalanteriorcingulate , wm_rh_caudalmiddlefrontal , wm_rh_cuneus , wm_rh_entorhinal , wm_rh_frontalpole , wm_rh_fusiform , wm_rh_inferiorparietal , wm_rh_inferiortemporal , wm_rh_insula , wm_rh_isthmuscingulate , wm_rh_lateraloccipital , wm_rh_lateralorbitofrontal , wm_rh_lingual , wm_rh_medialorbitofrontal , wm_rh_middletemporal , wm_rh_paracentral , wm_rh_parahippocampal , wm_rh_parsopercularis , wm_rh_parsorbitalis , wm_rh_parstriangularis , wm_rh_pericalcarine , wm_rh_postcentral , wm_rh_posteriorcingulate , wm_rh_precentral , wm_rh_precuneus , wm_rh_rostralanteriorcingulate , wm_rh_rostralmiddlefrontal , wm_rh_superiorfrontal , wm_rh_superiorparietal , wm_rh_superiortemporal , wm_rh_supramarginal , wm_rh_temporalpole , wm_rh_transversetemporal) SELECT subjectID , ICV , Left_Cerebral_White_Matter , Left_Cerebral_Cortex , Left_Lateral_Ventricle , Left_Inf_Lat_Vent , Left_Cerebellum_White_Matter , Left_Cerebellum_Cortex , Left_Thalamus_Proper , Left_Caudate , Left_Putamen , Left_Pallidum , Third_Ventricle , Fourth_Ventricle , Brain_Stem , Left_Hippocampus , Left_Amygdala , CSF , Left_Accumbens_area , Left_VentralDC , Left_choroid_plexus , Right_Cerebral_White_Matter , Right_Cerebral_Cortex , Right_Lateral_Ventricle , Right_Inf_Lat_Vent , Right_Cerebellum_White_Matter , Right_Cerebellum_Cortex , Right_Thalamus_Proper , Right_Caudate , Right_Putamen , Right_Pallidum , Right_Hippocampus , Right_Amygdala , Right_Accumbens_area , Right_VentralDC , Right_choroid_plexus , WM_hypointensities , non_WM_hypointensities , Optic_Chiasm , CC_Posterior , CC_Mid_Posterior , CC_Central , CC_Mid_Anterior , CC_Anterior , Left_UnsegmentedWhiteMatter , Right_UnsegmentedWhiteMatter , ctx_lh_bankssts , ctx_lh_caudalanteriorcingulate , ctx_lh_caudalmiddlefrontal , ctx_lh_cuneus , ctx_lh_entorhinal , ctx_lh_frontalpole , ctx_lh_fusiform , ctx_lh_inferiorparietal , ctx_lh_inferiortemporal , ctx_lh_insula , ctx_lh_isthmuscingulate , ctx_lh_lateraloccipital , ctx_lh_lateralorbitofrontal , ctx_lh_lingual , ctx_lh_medialorbitofrontal , ctx_lh_middletemporal , ctx_lh_paracentral , ctx_lh_parahippocampal , ctx_lh_parsopercularis , ctx_lh_parsorbitalis , ctx_lh_parstriangularis , ctx_lh_pericalcarine , ctx_lh_postcentral , ctx_lh_posteriorcingulate , ctx_lh_precentral , ctx_lh_precuneus , ctx_lh_rostralanteriorcingulate , ctx_lh_rostralmiddlefrontal , ctx_lh_superiorfrontal , ctx_lh_superiorparietal , ctx_lh_superiortemporal , ctx_lh_supramarginal , ctx_lh_temporalpole , ctx_lh_transversetemporal , ctx_lh_unknown , ctx_rh_bankssts , ctx_rh_caudalanteriorcingulate , ctx_rh_caudalmiddlefrontal , ctx_rh_cuneus , ctx_rh_entorhinal , ctx_rh_frontalpole , ctx_rh_fusiform , ctx_rh_inferiorparietal , ctx_rh_inferiortemporal , ctx_rh_insula , ctx_rh_isthmuscingulate , ctx_rh_lateraloccipital , ctx_rh_lateralorbitofrontal , ctx_rh_lingual , ctx_rh_medialorbitofrontal , ctx_rh_middletemporal , ctx_rh_paracentral , ctx_rh_parahippocampal , ctx_rh_parsopercularis , ctx_rh_parsorbitalis , ctx_rh_parstriangularis , ctx_rh_pericalcarine , ctx_rh_postcentral , ctx_rh_posteriorcingulate , ctx_rh_precentral , ctx_rh_precuneus , ctx_rh_rostralanteriorcingulate , ctx_rh_rostralmiddlefrontal , ctx_rh_superiorfrontal , ctx_rh_superiorparietal , ctx_rh_superiortemporal , ctx_rh_supramarginal , ctx_rh_temporalpole , ctx_rh_transversetemporal , ctx_rh_unknown , wm_lh_bankssts , wm_lh_caudalanteriorcingulate , wm_lh_caudalmiddlefrontal , wm_lh_cuneus , wm_lh_entorhinal , wm_lh_frontalpole , wm_lh_fusiform , wm_lh_inferiorparietal , wm_lh_inferiortemporal , wm_lh_insula , wm_lh_isthmuscingulate , wm_lh_lateraloccipital , wm_lh_lateralorbitofrontal , wm_lh_lingual , wm_lh_medialorbitofrontal , wm_lh_middletemporal , wm_lh_paracentral , wm_lh_parahippocampal , wm_lh_parsopercularis , wm_lh_parsorbitalis , wm_lh_parstriangularis , wm_lh_pericalcarine , wm_lh_postcentral , wm_lh_posteriorcingulate , wm_lh_precentral , wm_lh_precuneus , wm_lh_rostralanteriorcingulate , wm_lh_rostralmiddlefrontal , wm_lh_superiorfrontal , wm_lh_superiorparietal , wm_lh_superiortemporal , wm_lh_supramarginal , wm_lh_temporalpole , wm_lh_transversetemporal , wm_rh_bankssts , wm_rh_caudalanteriorcingulate , wm_rh_caudalmiddlefrontal , wm_rh_cuneus , wm_rh_entorhinal , wm_rh_frontalpole , wm_rh_fusiform , wm_rh_inferiorparietal , wm_rh_inferiortemporal , wm_rh_insula , wm_rh_isthmuscingulate , wm_rh_lateraloccipital , wm_rh_lateralorbitofrontal , wm_rh_lingual , wm_rh_medialorbitofrontal , wm_rh_middletemporal , wm_rh_paracentral , wm_rh_parahippocampal , wm_rh_parsopercularis , wm_rh_parsorbitalis , wm_rh_parstriangularis , wm_rh_pericalcarine , wm_rh_postcentral , wm_rh_posteriorcingulate , wm_rh_precentral , wm_rh_precuneus , wm_rh_rostralanteriorcingulate , wm_rh_rostralmiddlefrontal , wm_rh_superiorfrontal , wm_rh_superiorparietal , wm_rh_superiortemporal , wm_rh_supramarginal , wm_rh_temporalpole , wm_rh_transversetemporal FROM secondDB.examData WHERE subjectID IN (101, 127, 177, 217, 258, 361, 406,500);

Thank you,

Roman

(2) By Roman (moskvich412) on 2020-12-29 23:37:32 in reply to 1 [link] [source]

Dear SQLiters,

After I submitted this question to the Forum, I noticed it reformatted by removing spaces and lines. My original INSERT and SELECT had one column name per line. I copied and pasted the trimmed code from Forum to sqlite3 shell and it worked without errors. Therefore, issue is somehow related to processing of spaces and lines and maybe cause is ncursors or some other i/o library? I am using CentOS (copy and paste the SQL query from a text editor):

lsb_release -a

LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: CentOS

Description: CentOS release 6.9 (Final) Red Hat Enterprise Linux

Release: 6.9

Codename: Final

Thank you,

Roman

(3) By Stephan Beal (stephan) on 2020-12-30 00:03:17 in reply to 2 [link] [source]

After I submitted this question to the Forum, I noticed it reformatted by removing spaces and lines.

Non-anonymous users can edit their posts so long as the post is not awaiting moderation. You simply need to go back and change the post's format to "plain text" (the default is markdown format), then will appear verbatim as you entered it. That will cause it to await moderation again, but will fix the formatting. Alternately, an administrator with sufficient rights can edit the post to toggle the format.

(4) By Roman (moskvich412) on 2020-12-30 00:28:23 in reply to 3 [link] [source]

Thank you for pointing out choices of the "Markup style".
Here is unformated (with spaces, one column per line) that causes issues in sqlite3 shell:

INSERT OR IGNORE INTO main.examData(
                      subjectID ,
                            ICV ,
     Left_Cerebral_White_Matter ,
           Left_Cerebral_Cortex ,
         Left_Lateral_Ventricle ,
              Left_Inf_Lat_Vent ,
   Left_Cerebellum_White_Matter ,
         Left_Cerebellum_Cortex ,
           Left_Thalamus_Proper ,
                   Left_Caudate ,
                   Left_Putamen ,
                  Left_Pallidum ,
                Third_Ventricle ,
               Fourth_Ventricle ,
                     Brain_Stem ,
               Left_Hippocampus ,
                  Left_Amygdala ,
                            CSF ,
            Left_Accumbens_area ,
                 Left_VentralDC ,
            Left_choroid_plexus ,
    Right_Cerebral_White_Matter ,
          Right_Cerebral_Cortex ,
        Right_Lateral_Ventricle ,
             Right_Inf_Lat_Vent ,
  Right_Cerebellum_White_Matter ,
        Right_Cerebellum_Cortex ,
          Right_Thalamus_Proper ,
                  Right_Caudate ,
                  Right_Putamen ,
                 Right_Pallidum ,
              Right_Hippocampus ,
                 Right_Amygdala ,
           Right_Accumbens_area ,
                Right_VentralDC ,
           Right_choroid_plexus ,
             WM_hypointensities ,
         non_WM_hypointensities ,
                   Optic_Chiasm ,
                   CC_Posterior ,
               CC_Mid_Posterior ,
                     CC_Central ,
                CC_Mid_Anterior ,
                    CC_Anterior ,
    Left_UnsegmentedWhiteMatter ,
   Right_UnsegmentedWhiteMatter ,
                ctx_lh_bankssts ,
 ctx_lh_caudalanteriorcingulate ,
     ctx_lh_caudalmiddlefrontal ,
                  ctx_lh_cuneus ,
              ctx_lh_entorhinal ,
             ctx_lh_frontalpole ,
                ctx_lh_fusiform ,
        ctx_lh_inferiorparietal ,
        ctx_lh_inferiortemporal ,
                  ctx_lh_insula ,
        ctx_lh_isthmuscingulate ,
        ctx_lh_lateraloccipital ,
    ctx_lh_lateralorbitofrontal ,
                 ctx_lh_lingual ,
     ctx_lh_medialorbitofrontal ,
          ctx_lh_middletemporal ,
             ctx_lh_paracentral ,
         ctx_lh_parahippocampal ,
         ctx_lh_parsopercularis ,
           ctx_lh_parsorbitalis ,
        ctx_lh_parstriangularis ,
           ctx_lh_pericalcarine ,
             ctx_lh_postcentral ,
      ctx_lh_posteriorcingulate ,
              ctx_lh_precentral ,
               ctx_lh_precuneus ,
ctx_lh_rostralanteriorcingulate ,
    ctx_lh_rostralmiddlefrontal ,
         ctx_lh_superiorfrontal ,
        ctx_lh_superiorparietal ,
        ctx_lh_superiortemporal ,
           ctx_lh_supramarginal ,
            ctx_lh_temporalpole ,
      ctx_lh_transversetemporal ,
                 ctx_lh_unknown ,
                ctx_rh_bankssts ,
 ctx_rh_caudalanteriorcingulate ,
     ctx_rh_caudalmiddlefrontal ,
                  ctx_rh_cuneus ,
              ctx_rh_entorhinal ,
             ctx_rh_frontalpole ,
                ctx_rh_fusiform ,
        ctx_rh_inferiorparietal ,
        ctx_rh_inferiortemporal ,
                  ctx_rh_insula ,
        ctx_rh_isthmuscingulate ,
        ctx_rh_lateraloccipital ,
    ctx_rh_lateralorbitofrontal ,
                 ctx_rh_lingual ,
     ctx_rh_medialorbitofrontal ,
          ctx_rh_middletemporal ,
             ctx_rh_paracentral ,
         ctx_rh_parahippocampal ,
         ctx_rh_parsopercularis ,
           ctx_rh_parsorbitalis ,
        ctx_rh_parstriangularis ,
           ctx_rh_pericalcarine ,
             ctx_rh_postcentral ,
      ctx_rh_posteriorcingulate ,
              ctx_rh_precentral ,
               ctx_rh_precuneus ,
ctx_rh_rostralanteriorcingulate ,
    ctx_rh_rostralmiddlefrontal ,
         ctx_rh_superiorfrontal ,
        ctx_rh_superiorparietal ,
        ctx_rh_superiortemporal ,
           ctx_rh_supramarginal ,
            ctx_rh_temporalpole ,
      ctx_rh_transversetemporal ,
                 ctx_rh_unknown ,
                 wm_lh_bankssts ,
  wm_lh_caudalanteriorcingulate ,
      wm_lh_caudalmiddlefrontal ,
                   wm_lh_cuneus ,
               wm_lh_entorhinal ,
              wm_lh_frontalpole ,
                 wm_lh_fusiform ,
         wm_lh_inferiorparietal ,
         wm_lh_inferiortemporal ,
                   wm_lh_insula ,
         wm_lh_isthmuscingulate ,
         wm_lh_lateraloccipital ,
     wm_lh_lateralorbitofrontal ,
                  wm_lh_lingual ,
      wm_lh_medialorbitofrontal ,
           wm_lh_middletemporal ,
              wm_lh_paracentral ,
          wm_lh_parahippocampal ,
          wm_lh_parsopercularis ,
            wm_lh_parsorbitalis ,
         wm_lh_parstriangularis ,
            wm_lh_pericalcarine ,
              wm_lh_postcentral ,
       wm_lh_posteriorcingulate ,
               wm_lh_precentral ,
                wm_lh_precuneus ,
 wm_lh_rostralanteriorcingulate ,
     wm_lh_rostralmiddlefrontal ,
          wm_lh_superiorfrontal ,
         wm_lh_superiorparietal ,
         wm_lh_superiortemporal ,
            wm_lh_supramarginal ,
             wm_lh_temporalpole ,
       wm_lh_transversetemporal ,
                 wm_rh_bankssts ,
  wm_rh_caudalanteriorcingulate ,
      wm_rh_caudalmiddlefrontal ,
                   wm_rh_cuneus ,
               wm_rh_entorhinal ,
              wm_rh_frontalpole ,
                 wm_rh_fusiform ,
         wm_rh_inferiorparietal ,
         wm_rh_inferiortemporal ,
                   wm_rh_insula ,
         wm_rh_isthmuscingulate ,
         wm_rh_lateraloccipital ,
     wm_rh_lateralorbitofrontal ,
                  wm_rh_lingual ,
      wm_rh_medialorbitofrontal ,
           wm_rh_middletemporal ,
              wm_rh_paracentral ,
          wm_rh_parahippocampal ,
          wm_rh_parsopercularis ,
            wm_rh_parsorbitalis ,
         wm_rh_parstriangularis ,
            wm_rh_pericalcarine ,
              wm_rh_postcentral ,
       wm_rh_posteriorcingulate ,
               wm_rh_precentral ,
                wm_rh_precuneus ,
 wm_rh_rostralanteriorcingulate ,
     wm_rh_rostralmiddlefrontal ,
          wm_rh_superiorfrontal ,
         wm_rh_superiorparietal ,
         wm_rh_superiortemporal ,
            wm_rh_supramarginal ,
             wm_rh_temporalpole ,
       wm_rh_transversetemporal)
SELECT 
                      subjectID ,
                            ICV ,
     Left_Cerebral_White_Matter ,
           Left_Cerebral_Cortex ,
         Left_Lateral_Ventricle ,
              Left_Inf_Lat_Vent ,
   Left_Cerebellum_White_Matter ,
         Left_Cerebellum_Cortex ,
           Left_Thalamus_Proper ,
                   Left_Caudate ,
                   Left_Putamen ,
                  Left_Pallidum ,
                Third_Ventricle ,
               Fourth_Ventricle ,
                     Brain_Stem ,
               Left_Hippocampus ,
                  Left_Amygdala ,
                            CSF ,
            Left_Accumbens_area ,
                 Left_VentralDC ,
            Left_choroid_plexus ,
    Right_Cerebral_White_Matter ,
          Right_Cerebral_Cortex ,
        Right_Lateral_Ventricle ,
             Right_Inf_Lat_Vent ,
  Right_Cerebellum_White_Matter ,
        Right_Cerebellum_Cortex ,
          Right_Thalamus_Proper ,
                  Right_Caudate ,
                  Right_Putamen ,
                 Right_Pallidum ,
              Right_Hippocampus ,
                 Right_Amygdala ,
           Right_Accumbens_area ,
                Right_VentralDC ,
           Right_choroid_plexus ,
             WM_hypointensities ,
         non_WM_hypointensities ,
                   Optic_Chiasm ,
                   CC_Posterior ,
               CC_Mid_Posterior ,
                     CC_Central ,
                CC_Mid_Anterior ,
                    CC_Anterior ,
    Left_UnsegmentedWhiteMatter ,
   Right_UnsegmentedWhiteMatter ,
                ctx_lh_bankssts ,
 ctx_lh_caudalanteriorcingulate ,
     ctx_lh_caudalmiddlefrontal ,
                  ctx_lh_cuneus ,
              ctx_lh_entorhinal ,
             ctx_lh_frontalpole ,
                ctx_lh_fusiform ,
        ctx_lh_inferiorparietal ,
        ctx_lh_inferiortemporal ,
                  ctx_lh_insula ,
        ctx_lh_isthmuscingulate ,
        ctx_lh_lateraloccipital ,
    ctx_lh_lateralorbitofrontal ,
                 ctx_lh_lingual ,
     ctx_lh_medialorbitofrontal ,
          ctx_lh_middletemporal ,
             ctx_lh_paracentral ,
         ctx_lh_parahippocampal ,
         ctx_lh_parsopercularis ,
           ctx_lh_parsorbitalis ,
        ctx_lh_parstriangularis ,
           ctx_lh_pericalcarine ,
             ctx_lh_postcentral ,
      ctx_lh_posteriorcingulate ,
              ctx_lh_precentral ,
               ctx_lh_precuneus ,
ctx_lh_rostralanteriorcingulate ,
    ctx_lh_rostralmiddlefrontal ,
         ctx_lh_superiorfrontal ,
        ctx_lh_superiorparietal ,
        ctx_lh_superiortemporal ,
           ctx_lh_supramarginal ,
            ctx_lh_temporalpole ,
      ctx_lh_transversetemporal ,
                 ctx_lh_unknown ,
                ctx_rh_bankssts ,
 ctx_rh_caudalanteriorcingulate ,
     ctx_rh_caudalmiddlefrontal ,
                  ctx_rh_cuneus ,
              ctx_rh_entorhinal ,
             ctx_rh_frontalpole ,
                ctx_rh_fusiform ,
        ctx_rh_inferiorparietal ,
        ctx_rh_inferiortemporal ,
                  ctx_rh_insula ,
        ctx_rh_isthmuscingulate ,
        ctx_rh_lateraloccipital ,
    ctx_rh_lateralorbitofrontal ,
                 ctx_rh_lingual ,
     ctx_rh_medialorbitofrontal ,
          ctx_rh_middletemporal ,
             ctx_rh_paracentral ,
         ctx_rh_parahippocampal ,
         ctx_rh_parsopercularis ,
           ctx_rh_parsorbitalis ,
        ctx_rh_parstriangularis ,
           ctx_rh_pericalcarine ,
             ctx_rh_postcentral ,
      ctx_rh_posteriorcingulate ,
              ctx_rh_precentral ,
               ctx_rh_precuneus ,
ctx_rh_rostralanteriorcingulate ,
    ctx_rh_rostralmiddlefrontal ,
         ctx_rh_superiorfrontal ,
        ctx_rh_superiorparietal ,
        ctx_rh_superiortemporal ,
           ctx_rh_supramarginal ,
            ctx_rh_temporalpole ,
      ctx_rh_transversetemporal ,
                 ctx_rh_unknown ,
                 wm_lh_bankssts ,
  wm_lh_caudalanteriorcingulate ,
      wm_lh_caudalmiddlefrontal ,
                   wm_lh_cuneus ,
               wm_lh_entorhinal ,
              wm_lh_frontalpole ,
                 wm_lh_fusiform ,
         wm_lh_inferiorparietal ,
         wm_lh_inferiortemporal ,
                   wm_lh_insula ,
         wm_lh_isthmuscingulate ,
         wm_lh_lateraloccipital ,
     wm_lh_lateralorbitofrontal ,
                  wm_lh_lingual ,
      wm_lh_medialorbitofrontal ,
           wm_lh_middletemporal ,
              wm_lh_paracentral ,
          wm_lh_parahippocampal ,
          wm_lh_parsopercularis ,
            wm_lh_parsorbitalis ,
         wm_lh_parstriangularis ,
            wm_lh_pericalcarine ,
              wm_lh_postcentral ,
       wm_lh_posteriorcingulate ,
               wm_lh_precentral ,
                wm_lh_precuneus ,
 wm_lh_rostralanteriorcingulate ,
     wm_lh_rostralmiddlefrontal ,
          wm_lh_superiorfrontal ,
         wm_lh_superiorparietal ,
         wm_lh_superiortemporal ,
            wm_lh_supramarginal ,
             wm_lh_temporalpole ,
       wm_lh_transversetemporal ,
                 wm_rh_bankssts ,
  wm_rh_caudalanteriorcingulate ,
      wm_rh_caudalmiddlefrontal ,
                   wm_rh_cuneus ,
               wm_rh_entorhinal ,
              wm_rh_frontalpole ,
                 wm_rh_fusiform ,
         wm_rh_inferiorparietal ,
         wm_rh_inferiortemporal ,
                   wm_rh_insula ,
         wm_rh_isthmuscingulate ,
         wm_rh_lateraloccipital ,
     wm_rh_lateralorbitofrontal ,
                  wm_rh_lingual ,
      wm_rh_medialorbitofrontal ,
           wm_rh_middletemporal ,
              wm_rh_paracentral ,
          wm_rh_parahippocampal ,
          wm_rh_parsopercularis ,
            wm_rh_parsorbitalis ,
         wm_rh_parstriangularis ,
            wm_rh_pericalcarine ,
              wm_rh_postcentral ,
       wm_rh_posteriorcingulate ,
               wm_rh_precentral ,
                wm_rh_precuneus ,
 wm_rh_rostralanteriorcingulate ,
     wm_rh_rostralmiddlefrontal ,
          wm_rh_superiorfrontal ,
         wm_rh_superiorparietal ,
         wm_rh_superiortemporal ,
            wm_rh_supramarginal ,
             wm_rh_temporalpole ,
       wm_rh_transversetemporal 
  FROM secondDB.examData
 WHERE subjectID IN (101, 127, 177, 217, 258, 361, 406,500);


Roman

(5) By Richard Hipp (drh) on 2020-12-30 00:47:47 in reply to 4 [link] [source]

I copy/pasted your INSERT statement into a file, with a header that reads:

.open x1a.db
ATTACH 'x1b.db' AS secondDB;

Then did:

sqlite3 <x2.txt

Works perfectly every time, when I try it. I don't know what might be going wrong on your system.

(6) By Roman (moskvich412) on 2020-12-30 01:40:48 in reply to 5 [link] [source]

Thank you, Richard et al.

I should have tried on another system myself. I did now on Ubuntu 16 and it works fine. It must be old CentOS issue.

Thank you and Happy New Year!

Roman

(7) By Gunter Hick (gunter_hick) on 2020-12-30 06:58:36 in reply to 4 [source]

I could not avoid noticing that your table is severely de-normalized in that most of the column names seem to be "exam topics". Normalization would suggest having a topics table (id integer, title text) and making the exam_data a relation (student_id integer, topic_id integer, score integer) with foreign keys