13

Is there a way to create an FTS table in SQLite that is pre-populated with data from a SELECT query?

I know it’s possible to create a regular table that is prepopulated with data from a SELECT: CREATE TABLE foo AS SELECT ref_id, name FROM other_table

And we can create an FTS table like so: CREATE VIRTUAL TABLE bar USING FTS3(ref_id, name)

The point of doing this is to update my app’s SQLite database schema while avoiding reading in all of the data from other_table. I’m really hoping there’s some way to let SQLite do all the heavy lifting here (which is what it's really good at!).

David Cairns
  • 603
  • 5
  • 18

1 Answers1

30

I'm not sure if you can do it in one statement, but you can do it in two... after your CREATE VIRTUAL TABLE statement, you can do: INSERT INTO bar SELECT * FROM other_table

RBerteig
  • 41,948
  • 7
  • 88
  • 128
Mike W.
  • 1,345
  • 8
  • 18
  • Thanks! I actually just discovered that myself. I’m guessing it’s likely the most efficient way… MUCH more so than looping over the results from a select in my C code. – David Cairns Sep 19 '11 at 21:33