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

irisdb <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dplyr::copy_to(irisdb, iris[1,], name = "iris", overwrite = TRUE)
insert_table(iris[-1,], "iris", irisdb)
#> [1] 149
dplyr::tbl(irisdb, "iris")
#> # Source:   table<`iris`> [?? x 5]
#> # Database: sqlite 3.47.1 [:memory:]
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>           <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#>  1          5.1         3.5          1.4         0.2 setosa 
#>  2          4.9         3            1.4         0.2 setosa 
#>  3          4.7         3.2          1.3         0.2 setosa 
#>  4          4.6         3.1          1.5         0.2 setosa 
#>  5          5           3.6          1.4         0.2 setosa 
#>  6          5.4         3.9          1.7         0.4 setosa 
#>  7          4.6         3.4          1.4         0.3 setosa 
#>  8          5           3.4          1.5         0.2 setosa 
#>  9          4.4         2.9          1.4         0.2 setosa 
#> 10          4.9         3.1          1.5         0.1 setosa 
#> # ℹ more rows