Insert R data frame into SQL database

First, subset to matching columns. Then, make sure the local and SQL column classes match, coercing local to SQL as necessary (or throwing an error). Then, build an SQL string for the insert statement. Finally, insert into the database.

insert_table(values, table, con, coerce_col_class = TRUE, drop = TRUE)

Arguments

values	`data.frame` of values to write to SQL database
table	Name of target SQL table, as character
con	database connection object
coerce_col_class	logical, whether or not to coerce local data columns to SQL classes. Default = `TRUE.`
drop	logical. If `TRUE` (default), drop columns not found in SQL table.

Value

data frame with query results

Examples

# NOT RUN {
irisdb <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dplyr::copy_to(irisdb, iris[1,], name = "iris", overwrite = TRUE)
insert_table(iris[-1,], "iris", irisdb)
dplyr::tbl(irisdb, "iris")
# }