Simple SQL Lite table/import question

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 INTEGE开发者_如何学CR 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.

Instead of insert use

create table newtable as select  * from  footmp;

It is better and quicker.

With the .version of sqlite (SQLite 3.7.15.2 2013-01-09) I'm on, you don't have to import into a temp table first. All I did was to make sure I set a separator before starting the import. I did specify the id values for each row, so, my first column in the csv was a set of unique integers