Save Data Frame to Database Table

Source: R/hsPutTable.R

Writes data to a database table. This function performs opening of the connection, saving of the data to a table and closing of the connection. If an error occurs the program stops and an error description is shown. If a table named tbl already exists in the database mdb the existing table is only overwritten if overwrite is TRUE. Otherwise a hsSafeName will be found for the table.

hsPutTable(
  mdb,
  myData,
  tbl = "tblTmp",
  types = NULL,
  overwrite = FALSE,
  DBMSencoding = "",
  dbg = TRUE
)

Arguments

mdb

full path to MS Access database file (*.mdb)
myData

data.frame containing data to be written to database table
tbl

Name of table to be created in the database
types

field types to be passed to sqlSave as argument varTypes, see ?sqlSave for details.
overwrite

shall existing table be overwritten?
DBMSencoding

finally passed to odbcDriverConnect. Default: "", You may want to use: "UTF-8"
dbg

if TRUE, debug messages are shown

Value

In case of success the name of the created table is returned.

See also

hsSqlQuery, hsGetTable

Examples

if (FALSE) {
## Create a data.frame
df1 <- data.frame(id = 1:4, rnd = sample(1:100)[1:4])

## Write data.frame into a table in the example database; as no
## table name is specified, a table name is generated. The name
## of the table is returned.
## (only on Windows!)

if (.Platform$OS.type == "windows") {

  tbl <- hsPutTable(xmdb(), df1)
  tbl # table name here: [1] "tblTmp"


  ## Get the data from the created table back again and print the
  ## data. As we see, a table containing four different random
  ## numbers between one and 100 has been created.
  df2 <- hsGetTable(xmdb(), tbl)
  df2
}

## Output:
#   id rnd
# 1  1  82
# 2  2  14
# 3  3  99
# 4  4   6
}