Merge local data frame into SQL table

Merge local data frame into SQL table

Usage

db_merge_into(values, table, con, by = NULL, drop = FALSE, ...)

Arguments

values

`data.frame` of values to write to SQL database

table

Name of target SQL table, as character

con

Database connection object

by

Character vector of columns by which to perform merge. Defaults to all columns in `values`

drop

logical. If `TRUE` (default), drop columns not found in SQL table.

...

Arguments passed on to insert_table

coerce_col_class

logical, whether or not to coerce local data columns to SQL classes. Default = `TRUE.`

Value

Data frame: Inner join of SQL table and input data frame (as unevaluated "lazy query" table)

Examples

irisdb <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dplyr::copy_to(irisdb, iris[1:10,], name = "iris", overwrite = TRUE)
db_merge_into(iris[1:12,], "iris", irisdb)
#> Joining with `by = join_by(Sepal.Length, Sepal.Width, Petal.Length, Petal.Width, Species)`
#> # Source:   SQL [?? 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 
#> 11          5.4         3.7          1.5         0.2 setosa 
#> 12          4.8         3.4          1.6         0.2 setosa 
dplyr::tbl(irisdb, "iris") %>% dplyr::count()
#> # Source:   SQL [?? x 1]
#> # Database: sqlite 3.47.1 [:memory:]
#>       n
#>   <int>
#> 1    12