SQLite script vars

I need to mass populate my SQLite database — ideally using a script rather than code.

I would like to do this (MySQL syntax) but for SQLite but I'm not sure it you can have variables defined in scripts:

INSERT INTO `parent` (id, name) values(NULL, "some name!");
SET @parentId= last开发者_开发知识库_insert_rowid();
INSERT INTO `child` (id, parentId, name, ) values (NULL, @parentId, 'some name!);

SQLite throws errors when I try to declare variables in my SQLite script. Can this be done in SQLite?

You can use the function last_insert_rowid() without a script var for this case:

insert into parent (id, name) values (NULL, 'some name!');

then:

insert into child (id, parentId, name) values (NULL, last_insert_rowid(), 'child name!');

transcript:

SQLite version 3.7.6.3
sqlite> create table parent (id integer primary key, name);
sqlite> create table child (id integer primary key, parentId integer, name);
sqlite> insert into parent (id, name) values (NULL, 'some name!');
sqlite> insert into child (id, parentId, name) values (NULL, last_insert_rowid(), 'child name!');
sqlite> select * from parent;
1|some name!
sqlite> select * from child;
1|1|child name!
sqlite> 

If you need to keep the value around for a while (through multiple inserts for example) use a temporary table:

sqlite> create temp table stash (id integer primary key, parentId integer);
sqlite> insert into parent (id, name) values (NULL, 'another name!');
sqlite> replace into stash values (1, last_insert_rowid());
sqlite> insert into child (id, parentId, name) values (NULL, (select parentID from stash where id = 1), 'also a name!');
sqlite> select * from parent;
1|some name!
2|another name!
sqlite> select * from child;
1|1|child name!
2|2|also a name!
sqlite> 

Unfortunately you can't declare such variable in SQLite script. Moreover AFAIK all statements will not be executed except the first one. Also look HERE