This copy_to() method works for all DBI sources. 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 typically only visible to the current connection to the database.

# S3 method for src_sql
copy_to(dest, df, name = deparse(substitute(df)),
  overwrite = FALSE, types = NULL, temporary = TRUE,
  unique_indexes = NULL, indexes = NULL, analyze = TRUE, ...)

Arguments

dest

remote data source

df

A local data frame, a tbl_sql from same source, or a tbl_sql from 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

name for new remote table.

overwrite

If TRUE, will overwrite an existing table with name name. If FALSE, will throw an error if name already exists.

types

a character vector giving variable types to use for the columns. See http://www.sqlite.org/datatype3.html for available types.

temporary

if TRUE, will create a temporary table that is local to this connection and will be automatically deleted when the connection expires

unique_indexes

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.

indexes

a list of character vectors. Each element of the list will create a new index.

analyze

if TRUE (the default), will automatically ANALYZE the new table so that the query optimiser has useful information.

...

other parameters passed to methods.

Value

A tbl() object (invisibly).

Examples

library(dplyr)
#> #> Attaching package: ‘dplyr’
#> The following objects are masked from ‘package:dbplyr’: #> #> ident, sql
#> The following object is masked from ‘package:testthat’: #> #> matches
#> The following objects are masked from ‘package:stats’: #> #> filter, lag
#> The following objects are masked from ‘package:base’: #> #> intersect, setdiff, setequal, union
set.seed(1014) mtcars$model <- rownames(mtcars) mtcars2 <- src_memdb() %>% copy_to(mtcars, indexes = list("model"), overwrite = TRUE) mtcars2 %>% filter(model == "Hornet 4 Drive")
#> # Source: lazy query [?? x 12] #> # Database: sqlite 3.22.0 [:memory:] #> mpg cyl disp hp drat wt qsec vs am gear carb model #> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1 Hornet 4 Dr…
cyl8 <- mtcars2 %>% filter(cyl == 8) cyl8_cached <- copy_to(src_memdb(), cyl8) # copy_to is called automatically if you set copy = TRUE # in the join functions df <- tibble(cyl = c(6, 8)) mtcars2 %>% semi_join(df, copy = TRUE)
#> Joining, by = "cyl"
#> # Source: lazy query [?? x 12] #> # Database: sqlite 3.22.0 [:memory:] #> mpg cyl disp hp drat wt qsec vs am gear carb model #> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> #> 1 21 6 160 110 3.9 2.62 16.5 0 1 4 4 Mazda RX4 #> 2 21 6 160 110 3.9 2.88 17.0 0 1 4 4 Mazda RX4 … #> 3 21.4 6 258 110 3.08 3.22 19.4 1 0 3 1 Hornet 4 D… #> 4 18.7 8 360 175 3.15 3.44 17.0 0 0 3 2 Hornet Spo… #> 5 18.1 6 225 105 2.76 3.46 20.2 1 0 3 1 Valiant #> 6 14.3 8 360 245 3.21 3.57 15.8 0 0 3 4 Duster 360 #> 7 19.2 6 168. 123 3.92 3.44 18.3 1 0 4 4 Merc 280 #> 8 17.8 6 168. 123 3.92 3.44 18.9 1 0 4 4 Merc 280C #> 9 16.4 8 276. 180 3.07 4.07 17.4 0 0 3 3 Merc 450SE #> 10 17.3 8 276. 180 3.07 3.73 17.6 0 0 3 3 Merc 450SL #> # … with more rows