writeData: Write data to database

Description

Write data frame to database table via ODBC connection

Usage

writeData(MSSQLConnectionString = NULL, df, SQLiteFileName = NULL,
  tableName, addSAMUtilityColumns = FALSE, connectionString = NULL)

Arguments

MSSQLConnectionString

A string specifying the driver, server, database, and whether Windows Authentication will be used.

Dataframe that hold the tabular data

SQLiteFileName

A string. If dbtype is SQLite, here one specifies the database file to query from

tableName

String. Name of the table that receives the new rows

addSAMUtilityColumns

Boolean. Whether to add Health Catalyst-related date-time stamp, BindingID, and BindingNM to df before saving to db.

connectionString

Deprecated. A string specifying the driver, server, database, and whether Windows Authentication will be used. See ?MSSQLConnectionString instead.

Value

Nothing

References

http://healthcareai-r.readthedocs.io

Examples

Run this code

# NOT RUN {
# }
# NOT RUN {
# This example is specific to SQL Server.

# To instead pull data from Oracle see here 
# https://cran.r-project.org/web/packages/ROracle/ROracle.pdf
# To pull data from MySQL see here 
# https://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf
# To pull data from Postgres see here 
# https://cran.r-project.org/web/packages/RPostgreSQL/RPostgreSQL.pdf 

# Before running this example, create the table in SQL Server via
# CREATE TABLE [dbo].[HCRWriteData](
# [a] [float] NULL,
# [b] [float] NULL,
# [c] [varchar](255) NULL)

connectionString <- '
  driver={SQL Server};
  server=localhost;
  database=SAM;
  trustedConnection=true
  '

df <- data.frame(a=c(1,2,3),
                 b=c(2,4,6),
                 c=c('one','two','three'))

writeData(MSSQLConnectionString = connectionString, 
          df = df, 
          tableName = 'HCRWriteData')
# }
# NOT RUN {
# }
# NOT RUN {
#This example shows the RODBC way of writing to a non-default schema while 
#ODBC is being fixed. Here is a link to the non-default issue in ODBC: 
#https://github.com/rstats-db/odbc/issues/91

#First, create this table in SQL Server using a non-default schema. The 
#example creates this table in the SAM database on localhost. You will also 
#need to create a new schema(Cardiovascular) in SSMS for this specific 
#example to work.
#CREATE TABLE [Cardiovascular].[TestTable](
#[a] [float] NULL,
#[b] [float] NULL,
#[c] [varchar](255) NULL)

# Install the RODBC pacakge onto your machine. You only need to do this one 
# time.
#install.packages("RODBC")

# Load the package
library(RODBC)

# Create a connection to work with
con <- RODBC::odbcDriverConnect('driver={SQL Server};
                                server=localhost;
                                database=SAM;
                                trusted_connection=true')

# Df write to SQL Server. df columns names must match the SQL table in SSMS.
df <- data.frame(a = c(10, 20, 30),
                 b = c(20, 40, 60),
                 c = c("oneT", "twoT", "threeT"))

# Write the df to the SQL table                 
RODBC::sqlSave(con, df, "Cardiovascular.TestTable", append = TRUE, 
               rownames = FALSE)

# Verify that the table was written to 
confirmDf <- RODBC::sqlQuery(con, 'select * from Cardiovascular.TestTable') 
head(confirmDf)
# }