1

I have an sqlite database where I need to insert spatial information along with metadata into an R*tree and an accompanying regular table. Each entry needs to be uniquely defined for the lifetime of the database. Therefore the regular table have an INTEGER PRIMARY KEY AUTOINCREMENT column and my plan was to start with the insert into this table, extract the last inserted rowids and use these for the insert into the R*tree. Alas this doesn't seem possible:

>testCon <- dbConnect(RSQLite::SQLite(), ":memory:")
>dbGetQuery(testCon, 'CREATE TABLE testTable (x INTEGER PRIMARY KEY, y INTEGER)')
>dbGetQuery(testCon, 'INSERT INTO testTable (y) VALUES ($y)', bind.data=data.frame(y=1:5))
>dbGetQuery(testCon, 'SELECT last_insert_rowid() FROM testTable')

  last_insert_rowid()
1                   5
2                   5
3                   5
4                   5
5                   5

Only the last inserted rowid seems to be kept (probably for performance reasons). As the number of records to be inserted is hundreds of thousands, it is not feasible to do the insert line by line.

So the question is: Is there any way to make the last_insert_rowid() bend to my will? And if not, what is the best failsafe alternative? Some possibilities:

  • Record highest rowid before insert and 'SELECT rowid FROM testTable WHERE rowid > prevRowid'
  • Get the number of rows to insert, fetch the last_insert_rowid() and use seq(to=lastRowid, length.out=nInserts)

While the two above suggestion at least intuitively should work I don't feel confident enough in sqlite to know if they are failsafe.

ThomasP85
  • 1,624
  • 2
  • 15
  • 26

1 Answers1

0

The algorithm for generating autoincrementing IDs is documented.

For an INTEGER PRIMARY KEY column, you can simply get the current maximum value:

SELECT IFNULL(MAX(x), 0) FROM testTable

and then use the next values.

CL.
  • 173,858
  • 17
  • 217
  • 259