3

I have for example dataframe:

df <- as.data.frame(matrix(sample(c(NA, 1:50), 49, replace = TRUE), 7))

It looks like this:

  V1 V2 V3 V4 V5 V6 V7
1 46  6 23  7 22 42  1
2 47 33 47 50 42 NA 49
3 14 35 49 48 37 10 22
4 42 23  5  4 41 46 48
5 32 36 24 26 19 31 45
6 26 47 28 19 34 19 32
7 37 13 46 46 NA 22 49

Now I want to write this dataframe to an oracle database without using sqlSave because I have a huge data.frame and R Studio crashes if I do it. Instead I decided to do it with sqlQuery:

library(RODBC)
connHandle <- odbcConnect("DBName", uid="user", pwd="password")
sqlQuery(connHandle, sprintf("INSERT INTO MYTABLE VALUES %s", stringWithMyDataframeValues))
close(connHandle)

I have read this post but it doesn't work for me.

What is the optimal way of doing it? How should my string that I want to pass in look like? Thanks in advance.

Blind0ne
  • 1,015
  • 12
  • 28

1 Answers1

4

Assuming R dataframe columns are exactly the same columns and in same order in Oracle (not more or less), consider apply to paste with collapse all values in each row:

sqls <- sprintf("INSERT INTO MYTABLE VALUES (%s)", 
                apply(df, 1, function(i) paste(i, collapse=",")))    
sqls
# [1] "INSERT INTO MYTABLE VALUES (2,10,9,50,34,37,29)" 
# [2] "INSERT INTO MYTABLE VALUES (7,24,33,21,21,20,3)" 
# [3] "INSERT INTO MYTABLE VALUES (39,38,2,33,43,33,7)" 
# [4] "INSERT INTO MYTABLE VALUES (30,11,33,1,29,26,11)"
# [5] "INSERT INTO MYTABLE VALUES (50,45,13,27,3,35,36)"
# [6] "INSERT INTO MYTABLE VALUES (41,5,39,17,5,22,5)"  
# [7] "INSERT INTO MYTABLE VALUES (21,50,39,30,2,11,49)"

# RECOMMENDED APPROACH TO SPECIFY COLUMNS
sqls <- sprintf("INSERT INTO MYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES (%s)", 
                apply(df, 1, function(i) paste(i, collapse=",")))

connHandle <- odbcConnect("DBName", uid="user", pwd="password")
lapply(sqls, function(s) sqlQuery(connHandle, s))
close(connHandle)

And even better approach is to use parameterization with RODBCext where you just pass in original dataframe with no loop:

library(RODBCext)

connHandle <- odbcConnect("DBName", uid="user", pwd="password")
query <- "INSERT INTO MYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES (?, ?, ?, ?, ?, ?, ?)"
sqlExecute(connHandle, query, df)

odbcClose(connHandle)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • your solution worked good for me but `lapply` takes huge amount of time to upload my dataframe with 1.9 million rows of data. Do you have any alternative solution for this? – mockash May 14 '19 at 08:39
  • Consider exporting data to csv/txt from R, then importing using Oracle [SQL*Loader](http://www.orafaq.com/wiki/SQL%2aLoader_FAQ) tool. – Parfait May 14 '19 at 12:22
  • Yes came across `LOAD DATA` and am trying but getting error. – mockash May 14 '19 at 12:49
  • It's not an SQL command like in MySQL but a commandline utility for Oracle which cannot be run with RODBC. Read into it more and ask a separate question. Good luck! – Parfait May 14 '19 at 12:59
  • @Parfait the "RODBCext" does not work with latest R version; 3.5. "Package ‘RODBCext’ was removed from the CRAN repository." – CodeMaster Aug 24 '20 at 22:12
  • 1
    @user11397513,. yes, that can happen. This question is over 3 years old. But you can always install packagees from [Git sources](https://www.r-project.org/nosvn/pandoc/RODBCext.html). By the way, consider upgrading to R 4.0+. – Parfait Aug 25 '20 at 20:22