I have a simple SQL question. I want to make a 3 column database and I have the following code:
sqlite3 meshdb.db "create table t1 (t1key INTEGER PRIMARY KEY, prideID, pubmedID);"
When I try to import a simple csv file with two columns (prideID and pubmedID), I get a "expected 3 columns of data but found 2" error. I want the t1key to be an integer, and automatically count up as new fields are added. Do I have to put NOT NULL in front of PRIMARY KEY to for this to work?
.import does not support reshaping the input (except from setting the separator). You need to import the CSV file into a temporary table and the insert that into the real table. Here is a example session:
$ cat a.csv
1,2
3,4
5,6
$ sqlite3 a.db
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo(id integer primary key,x,y);
sqlite> create temp table footmp(x,y);
sqlite> .separator ,
sqlite> .import a.csv footmp
sqlite> select * from footmp;
1,2
3,4
5,6
sqlite> insert into foo(x,y) select * from footmp;
sqlite> select * from foo;
1,1,2
2,3,4
3,5,6
sqlite> drop table footmp;
You see that ID is counted up. This is because a column with type INTEGER PRIMARY KEY is treated as an alias for the internal ROWID - which always is a unique, ascending number.