Search code examples
mysqlrrmysql

How to write entire dataframe into mySql table in R

I have a data frame containing columns 'Quarter' having values like "16/17 Q1", "16/17 Q2"... and 'Vendor' having values like "a", "b"... .

I am trying to write this data frame into database using

query <- paste("INSERT INTO cc_demo (Quarter,Vendor) VALUES(dd$FY_QUARTER,dd$VENDOR.x)")

but it is throwing error :

Error in .local(conn, statement, ...) :

could not run statement: Unknown column 'dd$FY_QUARTER' in 'field list'

I am new to Rmysql, Please provide me some solution to write entire dataframe?

Solution

  • I would advise against writing sql query when you can actually use very handy functions such as dbWriteTable from the RMySQL package. But for the sake of practice, below is an example of how you should go about writing the sql query that does multiple inserts for a MySQL database:

    # Set up a data.frame
dd <- data.frame(Quarter = c("16/17 Q1", "16/17 Q2"), Vendors = c("a","b"))

# Begin the query
sql_qry <- "insert into cc_demo (Quarter,Vendor) VALUES"

# Finish it with
sql_qry <- paste0(sql_qry, paste(sprintf("('%s', '%s')", dd$Quarter, dd$Vendors), collapse = ","))

    You should get:

    "insert into cc_demo (Quarter,Vendor) VALUES('16/17 Q1', 'a'),('16/17 Q2', 'b')"

    You can provide this query to your database connection in order to run it.

    I hope this helps.