Menu
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!).
611
To insert data into a table, you use the INSERT statement. SQLite provides various forms of the INSERT statements that allow you to insert a single row, multiple rows, and default values into a table. In addition, you can insert a row into a table using data provided by a SELECT statement.
FTS5 is an SQLite virtual table module that provides full-text search functionality to database applications.
Overview. FTS3 and FTS4 are SQLite virtual table modules that allows users to perform full-text searches on a set of documents. The most common (and effective) way to describe full-text searches is "what Google, Yahoo, and Bing do with documents placed on the World Wide Web".
Every SQLite database has an SQLITE_SCHEMA table that defines the schema for the database. The SQLITE_SCHEMA table looks like this: CREATE TABLE sqlite_schema ( type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT );
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
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
