CODEWITHSUNDEEP
sqlite
Anthony Main
Anthony Main

Reputation: 6068

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

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

Upvotes: 1

Views: 2175

Answers (1)

Thomas Mueller
Thomas Mueller

Reputation: 50097

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

Upvotes: 6

Related Questions