how do I override the autoincremented primary key when doing an insert

Question

In MS SQL I would use

SET IDENTITY INSERT ON

How do I do something similar in SQLite. I am trying to upgrade a database and want to maintain the IDs from the original

Thanks

Answer 1

You don't need to set IDENTITY INSERT, because it is always possible to set the value explicitly. With SQLite, you can just insert into the ROWID column:

drop table test;
create table test(name varchar);
insert into test(name) values('Hello');
insert into test(rowid, name) values(10, 'World');
select rowid, name from test;

The same if you use an autoincrement primary key:

drop table test;
create table test(id integer primary key autoincrement, name varchar);
insert into test(name) values('Hello');
insert into test values(10, 'World');
select * from test;

See also http://www.sqlite.org/autoinc.html