How save data to SQL database quickly and in a bulk using R?

Question

I have following table:

structure(list(ID = c(9L, 9L, 8L, 8L, 8L, 8L, 8L, 8L, 13L), price= c(21040L, 
21056L, 63278L, 63280L, 63296L, 63312L, 63328L, 63344L, 32176L
)), class = "data.frame", row.names = c(NA, 9L))

I want to save it using following code

dbSendUpdate(CONNECTION,paste("INSERT INTO DATABASE  (Date, Id, Price) VALUES (cast(TIMESTAMP 'NOW' as date), ",
                           df$ID,  
                           ", ",
                           df$price,
                           
                           ");"))

However, when I ran this code, only the first row is saved to database. How can I save the whole table with one command?

do you use `DBI` with `RJDBC` as demonstrated [here](https://www.rforge.net/RJDBC/)? — Waldi, Mar 09 '21 at 09:43
does the table have exactly the columns you want to insert, or some other columns? — Waldi, Mar 09 '21 at 10:44

score 0 · Answer 1 · answered Mar 08 '21 at 17:16

0

You are just providing single row information to update into the table. You can look into below two link.

https://www.r-bloggers.com/2015/07/how-to-write-an-r-data-frame-to-an-sql-table/

How to insert data to SQL Server table using R?

answered Mar 08 '21 at 17:16

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

these methods show how to create a new 'temporary' table and then join it with the table in SQL database. However, I cannot create tables in the database, only writing to the existing ones. – Priit Mets Mar 09 '21 at 08:16

score 0 · Answer 2 · answered Mar 09 '21 at 10:53

0

You could use dbWriteTable if the table isn't yet created and dbAppendTable after creation of the table :

library(RJDBC)
library(DBI)

drv <- JDBC("Driver setup")
conn <- DBI::dbConnect(drv, "JDBC connection", "user", "pwd")

# If the table doesn't already exist
DBI::dbWriteTable(conn,"TABLENAME",df)

# For an existing table
DBI::dbAppendTable(conn, "TABLENAME",df)

Depending on DB system, an alternative to dbAppendTable is:

dbExecute(conn,sqlAppendTable(ANSI(), "TABLENAME", df))

answered Mar 09 '21 at 10:53

Waldi

39,242
6
30
78

Thank you for your help, however, I think this method works if I have the same number of columns and names in both SQL DB and R table. In reality, I have different names and a different numbers of columns. – Priit Mets Mar 09 '21 at 13:04
Yes, this was my question : I'm going to update this – Waldi Mar 09 '21 at 13:06
@priit Mets, I checked that the `dbExecute` solution also works when you don't supply all the columns, because [`sqlAppendTable` looks for the names of the supplied columns](https://github.com/r-dbi/DBI/blob/master/R/table-insert.R) . give it a try! – Waldi Mar 09 '21 at 17:09
`dbAppendTable` should also work because it relies on `sqlAppendTable` – Waldi Mar 09 '21 at 17:19
I tried `dbExecute(vertica,sqlAppendTable(ANSI(), "TABLENAME", df[2]))` (only prices from my df). and got error `Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set", : Unable to retrieve JDBC result set JDBC ERROR: [Vertica][VJDBC](4856) ERROR: Syntax error at or near "," Statement: INSERT INTO "MYTABLE" ("price") VALUES` – Priit Mets Mar 10 '21 at 16:06
I won't be able to help more as I tested this on a SQL Server connection relying on RODBC. RJDBC might have a different behaviour when using `sqlAppendTable` – Waldi Mar 10 '21 at 16:12

How save data to SQL database quickly and in a bulk using R?

2 Answers2