sqlAppendTable: Compose query to insert rows into a table

In DBI: R Database Interface

Description

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.

Usage

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

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

Arguments

con	A database connection.
table	The table name, passed on to dbQuoteIdentifier(). Options are: a character string with the unquoted DBMS table name, e.g. "table_name", a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name") a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')
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))

sqlAppendTable(ANSI(), "mtcars", head(mtcars))
sqlAppendTable(ANSI(), "mtcars", head(mtcars), row.names = FALSE)
sqlAppendTableTemplate(ANSI(), "iris", iris)

sqlAppendTableTemplate(ANSI(), "mtcars", mtcars)
sqlAppendTableTemplate(ANSI(), "mtcars", mtcars, row.names = FALSE)

Example output

<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')
<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)
<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)
<SQL> INSERT INTO "iris"
  ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
VALUES
  (?1, ?2, ?3, ?4, ?5)
<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)
<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)

DBI documentation built on Dec. 20, 2021, 9:07 a.m.