EVOLUTION-MANAGER
Edit File: dbWriteTable.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>R: Copy data frames to database tables</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="stylesheet" type="text/css" href="R.css" /> </head><body> <table width="100%" summary="page for dbWriteTable {DBI}"><tr><td>dbWriteTable {DBI}</td><td style="text-align: right;">R Documentation</td></tr></table> <h2>Copy data frames to database tables</h2> <h3>Description</h3> <p>Writes, overwrites or appends a data frame to a database table, optionally converting row names to a column and specifying SQL data types for fields. New code should prefer <code><a href="dbCreateTable.html">dbCreateTable()</a></code> and <code><a href="dbAppendTable.html">dbAppendTable()</a></code>. </p> <h3>Usage</h3> <pre> dbWriteTable(conn, name, value, ...) </pre> <h3>Arguments</h3> <table summary="R argblock"> <tr valign="top"><td><code>conn</code></td> <td> <p>A <a href="DBIConnection-class.html">DBIConnection</a> object, as returned by <code><a href="dbConnect.html">dbConnect()</a></code>.</p> </td></tr> <tr valign="top"><td><code>name</code></td> <td> <p>A character string specifying the unquoted DBMS table name, or the result of a call to <code><a href="dbQuoteIdentifier.html">dbQuoteIdentifier()</a></code>.</p> </td></tr> <tr valign="top"><td><code>value</code></td> <td> <p>a <a href="../../base/html/data.frame.html">data.frame</a> (or coercible to data.frame).</p> </td></tr> <tr valign="top"><td><code>...</code></td> <td> <p>Other parameters passed on to methods.</p> </td></tr> </table> <h3>Value</h3> <p><code>dbWriteTable()</code> returns <code>TRUE</code>, invisibly. If the table exists, and both <code>append</code> and <code>overwrite</code> arguments are unset, or <code>append = TRUE</code> and the data frame with the new data has different column names, an error is raised; the remote table remains unchanged. </p> <p>An error is raised when calling this method for a closed or invalid connection. An error is also raised if <code>name</code> cannot be processed with <code><a href="dbQuoteIdentifier.html">dbQuoteIdentifier()</a></code> or if this results in a non-scalar. Invalid values for the additional arguments <code>row.names</code>, <code>overwrite</code>, <code>append</code>, <code>field.types</code>, and <code>temporary</code> (non-scalars, unsupported data types, <code>NA</code>, incompatible values, duplicate or missing names, incompatible columns) also raise an error. </p> <h3>Additional arguments</h3> <p>The following arguments are not part of the <code>dbWriteTable()</code> generic (to improve compatibility across backends) but are part of the DBI specification: </p> <ul> <li> <p><code>row.names</code> (default: <code>FALSE</code>) </p> </li> <li> <p><code>overwrite</code> (default: <code>FALSE</code>) </p> </li> <li> <p><code>append</code> (default: <code>FALSE</code>) </p> </li> <li> <p><code>field.types</code> (default: <code>NULL</code>) </p> </li> <li> <p><code>temporary</code> (default: <code>FALSE</code>) </p> </li></ul> <p>They must be provided as named arguments. See the "Specification" and "Value" sections for details on their usage. </p> <h3>Specification</h3> <p>The <code>name</code> argument is processed as follows, to support databases that allow non-syntactic names for their objects: </p> <ul> <li><p> If an unquoted table name as string: <code>dbWriteTable()</code> will do the quoting, perhaps by calling <code>dbQuoteIdentifier(conn, x = name)</code> </p> </li> <li><p> If the result of a call to <code><a href="dbQuoteIdentifier.html">dbQuoteIdentifier()</a></code>: no more quoting is done </p> </li></ul> <p>If the <code>overwrite</code> argument is <code>TRUE</code>, an existing table of the same name will be overwritten. This argument doesn't change behavior if the table does not exist yet. </p> <p>If the <code>append</code> argument is <code>TRUE</code>, the rows in an existing table are preserved, and the new data are appended. If the table doesn't exist yet, it is created. </p> <p>If the <code>temporary</code> argument is <code>TRUE</code>, the table is not available in a second connection and is gone after reconnecting. Not all backends support this argument. A regular, non-temporary table is visible in a second connection and after reconnecting to the database. </p> <p>SQL keywords can be used freely in table names, column names, and data. Quotes, commas, and spaces can also be used in the data, and, if the database supports non-syntactic identifiers, also for table names and column names. </p> <p>The following data types must be supported at least, and be read identically with <code><a href="dbReadTable.html">dbReadTable()</a></code>: </p> <ul> <li><p> integer </p> </li> <li><p> numeric (the behavior for <code>Inf</code> and <code>NaN</code> is not specified) </p> </li> <li><p> logical </p> </li> <li> <p><code>NA</code> as NULL </p> </li> <li><p> 64-bit values (using <code>"bigint"</code> as field type); the result can be </p> <ul> <li><p> converted to a numeric, which may lose precision, </p> </li> <li><p> converted a character vector, which gives the full decimal representation </p> </li> <li><p> written to another table and read again unchanged </p> </li></ul> </li> <li><p> character (in both UTF-8 and native encodings), supporting empty strings </p> </li> <li><p> factor (returned as character) </p> </li> <li><p> list of raw (if supported by the database) </p> </li> <li><p> objects of type <a href="../../blob/html/blob.html">blob::blob</a> (if supported by the database) </p> </li> <li><p> date (if supported by the database; returned as <code>Date</code>) </p> </li> <li><p> time (if supported by the database; returned as objects that inherit from <code>difftime</code>) </p> </li> <li><p> timestamp (if supported by the database; returned as <code>POSIXct</code> respecting the time zone but not necessarily preserving the input time zone) </p> </li></ul> <p>Mixing column types in the same table is supported. </p> <p>The <code>field.types</code> argument must be a named character vector with at most one entry for each column. It indicates the SQL data type to be used for a new column. If a column is missed from <code>field.types</code>, the type is inferred from the input data with <code><a href="dbDataType.html">dbDataType()</a></code>. </p> <p>The interpretation of <a href="rownames.html">rownames</a> depends on the <code>row.names</code> argument, see <code><a href="rownames.html">sqlRownamesToColumn()</a></code> for details: </p> <ul> <li><p> If <code>FALSE</code> or <code>NULL</code>, row names are ignored. </p> </li> <li><p> If <code>TRUE</code>, row names are converted to a column named "row_names", even if the input data frame only has natural row names from 1 to <code>nrow(...)</code>. </p> </li> <li><p> If <code>NA</code>, a column named "row_names" is created if the data has custom row names, no extra column is created in the case of natural row names. </p> </li> <li><p> If a string, this specifies the name of the column in the remote table that contains the row names, even if the input data frame only has natural row names. </p> </li></ul> <p>The default is <code>row.names = FALSE</code>. </p> <h3>See Also</h3> <p>Other DBIConnection generics: <code><a href="DBIConnection-class.html">DBIConnection-class</a></code>, <code><a href="dbAppendTable.html">dbAppendTable</a>()</code>, <code><a href="dbCreateTable.html">dbCreateTable</a>()</code>, <code><a href="dbDataType.html">dbDataType</a>()</code>, <code><a href="dbDisconnect.html">dbDisconnect</a>()</code>, <code><a href="dbExecute.html">dbExecute</a>()</code>, <code><a href="dbExistsTable.html">dbExistsTable</a>()</code>, <code><a href="dbGetException.html">dbGetException</a>()</code>, <code><a href="dbGetInfo.html">dbGetInfo</a>()</code>, <code><a href="dbGetQuery.html">dbGetQuery</a>()</code>, <code><a href="dbIsReadOnly.html">dbIsReadOnly</a>()</code>, <code><a href="dbIsValid.html">dbIsValid</a>()</code>, <code><a href="dbListFields.html">dbListFields</a>()</code>, <code><a href="dbListObjects.html">dbListObjects</a>()</code>, <code><a href="dbListResults.html">dbListResults</a>()</code>, <code><a href="dbListTables.html">dbListTables</a>()</code>, <code><a href="dbReadTable.html">dbReadTable</a>()</code>, <code><a href="dbRemoveTable.html">dbRemoveTable</a>()</code>, <code><a href="dbSendQuery.html">dbSendQuery</a>()</code>, <code><a href="dbSendStatement.html">dbSendStatement</a>()</code> </p> <h3>Examples</h3> <pre> con <- dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con, "mtcars", mtcars[1:5, ]) dbReadTable(con, "mtcars") dbWriteTable(con, "mtcars", mtcars[6:10, ], append = TRUE) dbReadTable(con, "mtcars") dbWriteTable(con, "mtcars", mtcars[1:10, ], overwrite = TRUE) dbReadTable(con, "mtcars") # No row names dbWriteTable(con, "mtcars", mtcars[1:10, ], overwrite = TRUE, row.names = FALSE) dbReadTable(con, "mtcars") </pre> <hr /><div style="text-align: center;">[Package <em>DBI</em> version 1.1.0 <a href="00Index.html">Index</a>]</div> </body></html>