Insert multiple rows from R Dataframe into Oracle Database

Question

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.

Parfait · Accepted Answer · 2017-06-12 16:28:01Z

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)

answered Jun 12, 2017 at 16:28

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mockash Over a year ago

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?

Parfait Over a year ago

Consider exporting data to csv/txt from R, then importing using Oracle SQL*Loader tool.

mockash Over a year ago

Yes came across LOAD DATA and am trying but getting error.

Parfait Over a year ago

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 Over a year ago

@user11397513,. yes, that can happen. This question is over 3 years old. But you can always install packagees from Git sources. By the way, consider upgrading to R 4.0+.

|

Collectives™ on Stack Overflow

Insert multiple rows from R Dataframe into Oracle Database

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related