Copy a local data frame to a remote databaseSource:
It is useful for copying small amounts of data to a database for examples, experiments, and joins. By default, it creates temporary tables which are only visible within the current connection to the database.
remote data source
A local data frame, a
tbl_sqlfrom same source, or a
tbl_sqlfrom another source. If from another source, all data must transition through R in one pass, so it is only suitable for transferring small amounts of data.
name for new remote table.
TRUE, will overwrite an existing table with name
FALSE, will throw an error if
a character vector giving variable types to use for the columns. See https://www.sqlite.org/datatype3.html for available types.
TRUE, will create a temporary table that is local to this connection and will be automatically deleted when the connection expires
a list of character vectors. Each element of the list will create a new unique index over the specified column(s). Duplicate rows will result in failure.
a list of character vectors. Each element of the list will create a new index.
TRUE(the default), will automatically ANALYZE the new table so that the query optimiser has useful information.
other parameters passed to methods.
Should the table creation be wrapped in a transaction? This typically makes things faster, but you may want to suppress if the database doesn't support transactions, or you're wrapping in a transaction higher up (and your database doesn't support nested transactions.)
copy_inline() to use small data in an SQL query without actually
writing to a table.
library(dplyr, warn.conflicts = FALSE) df <- data.frame(x = 1:5, y = letters[5:1]) db <- copy_to(src_memdb(), df) db #> # Source: table<df> [5 x 2] #> # Database: sqlite 3.41.2 [:memory:] #> x y #> <int> <chr> #> 1 1 e #> 2 2 d #> 3 3 c #> 4 4 b #> 5 5 a df2 <- data.frame(y = c("a", "d"), fruit = c("apple", "date")) # copy_to() is called automatically if you set copy = TRUE # in the join functions db %>% left_join(df2, copy = TRUE) #> Joining with `by = join_by(y)` #> # Source: SQL [5 x 3] #> # Database: sqlite 3.41.2 [:memory:] #> x y fruit #> <int> <chr> <chr> #> 1 1 e NA #> 2 2 d date #> 3 3 c NA #> 4 4 b NA #> 5 5 a apple