Compose query to insert rows into a table

sqlAppendTable() generates a single SQL string that inserts a data frame into an existing table. sqlAppendTableTemplate() generates a template suitable for use with dbBind(). The default methods are ANSI SQL 99 compliant. These methods are mostly useful for backend implementers.

sqlAppendTable(con, table, values, row.names = NA, ...)

sqlAppendTableTemplate(
  con,
  table,
  values,
  row.names = NA,
  prefix = "?",
  ...,
  pattern = ""
)

Arguments

con	A database connection.
table	Name of the table. Escaped with dbQuoteIdentifier().
values	A data frame. Factors will be converted to character vectors. Character vectors will be escaped with dbQuoteString().
row.names	Either TRUE, FALSE, NA or a string. If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector. A string is equivalent to TRUE, but allows you to override the default name. For backward compatibility, NULL is equivalent to FALSE.
...	Other arguments used by individual methods.
prefix	Parameter prefix to use for placeholders.
pattern	Parameter pattern to use for placeholders: "": no pattern "1": position anything else: field name

Details

The row.names argument must be passed explicitly in order to avoid a compatibility warning. The default will be changed in a later release.

Examples

sqlAppendTable(ANSI(), "iris", head(iris))
#> Warning: Do not rely on the default value of the row.names argument for sqlAppendTable(), it will change in the future.
#> <SQL> INSERT INTO "iris"
#>   ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
#> VALUES
#>   (5.1, 3.5, 1.4, 0.2, 'setosa'),
#>   (4.9, 3, 1.4, 0.2, 'setosa'),
#>   (4.7, 3.2, 1.3, 0.2, 'setosa'),
#>   (4.6, 3.1, 1.5, 0.2, 'setosa'),
#>   (5, 3.6, 1.4, 0.2, 'setosa'),
#>   (5.4, 3.9, 1.7, 0.4, 'setosa')

sqlAppendTable(ANSI(), "mtcars", head(mtcars))
#> Warning: Do not rely on the default value of the row.names argument for sqlAppendTable(), it will change in the future.
#> <SQL> INSERT INTO "mtcars"
#>   ("row_names", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb")
#> VALUES
#>   ('Mazda RX4', 21, 6, 160, 110, 3.9, 2.62, 16.46, 0, 1, 4, 4),
#>   ('Mazda RX4 Wag', 21, 6, 160, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4),
#>   ('Datsun 710', 22.8, 4, 108, 93, 3.85, 2.32, 18.61, 1, 1, 4, 1),
#>   ('Hornet 4 Drive', 21.4, 6, 258, 110, 3.08, 3.215, 19.44, 1, 0, 3, 1),
#>   ('Hornet Sportabout', 18.7, 8, 360, 175, 3.15, 3.44, 17.02, 0, 0, 3, 2),
#>   ('Valiant', 18.1, 6, 225, 105, 2.76, 3.46, 20.22, 1, 0, 3, 1)
sqlAppendTable(ANSI(), "mtcars", head(mtcars), row.names = FALSE)
#> <SQL> INSERT INTO "mtcars"
#>   ("mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb")
#> VALUES
#>   (21, 6, 160, 110, 3.9, 2.62, 16.46, 0, 1, 4, 4),
#>   (21, 6, 160, 110, 3.9, 2.875, 17.02, 0, 1, 4, 4),
#>   (22.8, 4, 108, 93, 3.85, 2.32, 18.61, 1, 1, 4, 1),
#>   (21.4, 6, 258, 110, 3.08, 3.215, 19.44, 1, 0, 3, 1),
#>   (18.7, 8, 360, 175, 3.15, 3.44, 17.02, 0, 0, 3, 2),
#>   (18.1, 6, 225, 105, 2.76, 3.46, 20.22, 1, 0, 3, 1)
sqlAppendTableTemplate(ANSI(), "iris", iris)
#> Warning: Do not rely on the default value of the `row.names` argument to `sqlAppendTableTemplate()`, it will change in the future.
#> <SQL> INSERT INTO "iris"
#>   ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
#> VALUES
#>   (?, ?, ?, ?, ?)

sqlAppendTableTemplate(ANSI(), "mtcars", mtcars)
#> Warning: Do not rely on the default value of the `row.names` argument to `sqlAppendTableTemplate()`, it will change in the future.
#> <SQL> INSERT INTO "mtcars"
#>   ("row_names", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb")
#> VALUES
#>   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
sqlAppendTableTemplate(ANSI(), "mtcars", mtcars, row.names = FALSE)
#> <SQL> INSERT INTO "mtcars"
#>   ("mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb")
#> VALUES
#>   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)