R/sqlAppendTable.R
, R/sqlAppendTableTemplate.R
sqlAppendTable.Rd
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 = ""
)
A database connection.
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"')
A data frame. Factors will be converted to character vectors.
Character vectors will be escaped with dbQuoteString()
.
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.
Parameter prefix to use for placeholders.
Parameter pattern to use for placeholders:
""
: no pattern
"1"
: position
anything else: field name
The row.names
argument must be passed explicitly in order to avoid
a compatibility warning. The default will be changed in a later release.
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
#> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)