insert_values {sqlHelpers}

Generate a INSERT statement, optionally execute the statement if con is not NULL.

Description

Generate a INSERT statement, optionally execute the statement if con is not NULL.

Usage

insert_values(
  x = NULL,
  schema = NULL,
  table,
  returning = NULL,
  quote_text = TRUE,
  cast = TRUE,
  types = NULL,
  batch_size = 1000,
  con = NULL,
  table_is_temporary = FALSE,
  retain_insert_order = FALSE,
  n_cores = 1,
  connect_db_name = NULL,
  dialect = "T-SQL"
)

Arguments

x

A list, data.frame or data.table, names must match the column names of the destination SQL table.

schema

A string, the schema name of the destination SQL table.

table

A string, the table name of the destination SQL table.

returning

A vector of character strings specifying the SQL column names to be returned by the INSERT statement.

quote_text

TRUE/FALSE, if TRUE, calls quoteText() to add single quotes around character strings.

cast

TRUE/FALSE, if TRUE, will add SQL to cast the data to be inserted to the specified type.

types

A vector of types to use for casting columns. If blank, will look at meta data about table to decide types.

batch_size

Integer, the maximum number of records to submit in one statement.

con

A database connection that can be passed to DBI::dbSendQuery/DBI::dbGetQuery.

table_is_temporary

TRUE/FALSE, if TRUE, prevents parallel processing.

retain_insert_order

TRUE/FALSE, if TRUE, prevents parallel processing.

n_cores

A integer, the number of cores to use for parallel forking (passed to parallel::mclapply as mc.cores).

connect_db_name

The name of the database to pass to connect() when inserting in parallel.

dialect

A string, "T-SQL" or "Postgresql".

Value

A string, the INSERT statement; or the results retrieved by DBI::dbGetQuery after executing the statement.

Examples

insert_values(
x = list(col1 = c("a", "b", "c"), col2 = c(1, 2, 3)),
schema = "test",
table = "table1",
types = c("VARCHAR(12)", "INT")
)