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

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

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

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 put in front of column id.

Examples

sqlAppendTable(ANSI(), "iris", head(iris))
#> <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))
#> <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)
#> <SQL> INSERT INTO "iris" #> ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species") #> VALUES #> (?1, ?2, ?3, ?4, ?5)
sqlAppendTableTemplate(ANSI(), "mtcars", mtcars)
#> <SQL> INSERT INTO "mtcars" #> ("row_names", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb") #> VALUES #> (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)
sqlAppendTableTemplate(ANSI(), "mtcars", mtcars, row.names = FALSE)
#> <SQL> INSERT INTO "mtcars" #> ("mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am", "gear", "carb") #> VALUES #> (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)