Appends data from a dataframe into an existing database table

Requires the dataframe and database table to have the same structure

Usage

cdr_append_tbl(db_tbl, conn_pool, db_tbl_name = NULL, chunk_size = NULL, ...)

Arguments

db_tbl

the dataframe to append to the database table

conn_pool

a database connection of class pool or DBI

db_tbl_name

the name of the database table if different from the name of the dataframe passed to 'db_tbl'; can alternatively accept a DBI::Id() or object

chunk_size

the maximum number of cells you want to pass to the DB in one go (i.e. if you have a dataframe with 100 columns and 10,000 rows, a chunk_size of 1000 elements would split the dataframe into 1000 SQL query groups and append each group successively. Default is an estimate for the number of cells that makes ~1,000,000 bytes.)

...

other args specifying a DB table such as schema = 'my_schema'

Value

invisibly returns the SQL INSERT statements

Examples

if (FALSE) {

con <- pool::dbPool(DBI::dbConnect(RSQLite::SQLite(), 'test.db'))
example_tbl <- dplyr::mutate(iris, bool = Species == 'setosa', day = Sys.Date(), test = Sys.time())
pool::dbCreateTable(con, 'example_tbl', example_tbl)
cdr_append_tbl(example_tbl, con)
dplyr::tbl(con, 'example_tbl')
pool::dbRemoveTable(con,'example_tbl')
pool::poolClose(con)

}