dbTableFromView: Create a table in a SQLite database from a view

In RSQLite.toolkit: Load Data in SQLite from Tabular Files

Create a table in a SQLite database from a view

Description

The dbTableFromView() function creates a table in a SQLite database from a view already present in the same database.

Usage

dbTableFromView(
  view_name,
  dbcon,
  table_name,
  drop_table = FALSE,
  build_pk = FALSE,
  pk_fields = NULL
)

Arguments

view_name	character, name of the view.
dbcon	database connection, as created by the dbConnect function.
table_name	character, the name of the table.
drop_table	logical, if TRUE the target table will be dropped (if exists) and recreated when importing the data. if FALSE, data from input file will be appended to an existing table. Defaults to FALSE.
build_pk	logical, if TRUE creates a ⁠UNIQUE INDEX⁠ named ⁠<table_name>_PK⁠ defined by the combination of fields specified in the pk_fields parameter. It will be effective only if pk_fields is not null. Defaults to FALSE.
pk_fields	character vector, the list of the fields' names that define the ⁠UNIQUE INDEX⁠. Defaults to NULL.

Value

integer, the number of records in table_name after writing data from the input view.

Examples

# Create a temporary database and demonstrate view to table conversion
library(RSQLite.toolkit)

# Set up database connection
dbcon <- dbConnect(RSQLite::SQLite(), file.path(tempdir(), "example.sqlite"))

# Load some sample data first
data_path <- system.file("extdata", package = "RSQLite.toolkit")
dbTableFromDSV(
  input_file = file.path(data_path, "abalone.csv"),
  dbcon = dbcon,
  table_name = "ABALONE",
  drop_table = TRUE,
  header = TRUE,
  sep = ",",
  dec = "."
)

# Create a view with aggregated data
dbExecute(dbcon, "DROP VIEW IF EXISTS VW_ABALONE_SUMMARY;")

dbExecute(dbcon, 
  "CREATE VIEW VW_ABALONE_SUMMARY AS 
   SELECT SEX, 
          COUNT(*) as COUNT, 
          AVG(LENGTH) as AVG_LENGTH,
          AVG(WHOLE) as AVG_WEIGHT
   FROM ABALONE 
   GROUP BY SEX"
)

# Convert the view to a permanent table
dbTableFromView(
  view_name = "VW_ABALONE_SUMMARY",
  dbcon = dbcon,
  table_name = "ABALONE_STATS",
  drop_table = TRUE
)

# Check the result
dbListTables(dbcon)
dbGetQuery(dbcon, "SELECT * FROM ABALONE_STATS")

# Clean up
dbDisconnect(dbcon)

RSQLite.toolkit documentation built on Sept. 2, 2026, 9:06 a.m.