How can I insert a complete R dataframe in one go into SQL table

Question

How can I insert a complete R data.frame in one go into a SQL table, with the help of a stored procedure but without using a loop, using R language?

I am using Microsoft Server 2012.

This is my R code:

library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=MySERVERName;database=Testing;trusted_connection=true;')
f= data.frame(
    age= c(21,22,23,24),
  name= c("fifi", "jojo", "jj", "arbi"), stringsAsFactors = FALSE)
sqlQuery(dbhandle,paste("EXEC BOinsert @age=", f$age, ", @name=", f$name)) 

This is my SP:

ALTER proc [dbo].[BOinsert]

@age int,
@name nvarchar(50)
as 
Insert into Student(Age, Name) values (@age, @name)

Answer 1

I would recommend using RODBCext.

library(RODBCext)

f= data.frame(
  age= c(21,22,23,24),
  name= c("fifi", "jojo", "jj", "arbi"), stringsAsFactors = FALSE)

sqlExecute(dbhandle,
           "EXEC BOinsert @age = ?, @name = ?",
           data = f)

Strictly speaking, this is running a for loop over your data and executing the stored procedure once for every row. But I think it is done in C code, and you don't have to worry about making it work. It's one line of code for you. It will also deal with all of the quoting for you so that you don't have to worry about SQL injection attacks.

EDIT:

Just ran a test on my system with a data frame of 1,000 rows. sqlExecute was able to load it all in about 1.52 seconds. It took sqlQuery about 1.76 seconds. So not a huge improvement performance, but at least sqlExecute doesn't look like a loop.

Answer 2

It can be done by MySql using RMySQL

install.packages("RMySQL")
library(RMySQL)
mydb = dbConnect(MySQL(), user='your_user_name', password='your_password', dbname='your_database_name', host='localhost') 

dbListTables(mydb)


#create data frame

ds <- data.frame(a=1:10, b= 21:30)

#adding ds data frame to your database
dbWriteTable(mydb, name='db', value=ds)

#then check your database this will be added as table in name of db

Answer 3

Added 02/22/2017:

There is no easy way avoid the loop if you cannot change the stored procedure ("SP") which can only insert one row per call. This means a network roundtrip per stored procedure call (= per data.frame row).

Your alternatives are (all too complicated to show it here in detail):

1. Bulk insert the data.frame into a temporary or staging table using bcp.exe or the bulk insert statement. Then execute a SQL statement the loops over all rows in the temp/staging table and calls your insert SP. This effectively shifts the loop from the client side (R) to the server side (SQL server) which is much faster since no network roundtrips are required per row to be inserted. For details see: MS-SQL Bulk Insert with RODBC

2. Still loop over all rows in the R code but create a block of SQL statements that insert many rows at once (e. g. 100 at time). This reduced the network roundtrips by the number of rows you send at once.

3. Create a wrapper SP that receives the data.frame content as e. g. CSV text string, parses it and calls your insert SP per data row.

4. [Added 02/23/2017] You could use the package RODBCext which supports prepared statements. This "only" hides the loop but still loops internally at the client side (with one network/server roundtrip per row insert) but saves some time since the SQL statement must not be parsed and "prepared" again (execution planning etc.).

Solutions 2 is the easiest one to implement, but you have to care about the maximum statement length of the ODBC driver (about 1000, 2000, 4000 or 8000 character I guess).

Original answer:

Since you have to use stored procedure to insert each single row you end up with a slow looped solution (which is OK if you do not have to many rows to insert):

library(RODBC)

f <- data.frame(age = c(21,22,23,24), name = c("fifi", "jojo", "jj", "arbi"), stringsAsFactors = FALSE)

dbhandle <- odbcDriverConnect('driver={SQL Server};server=MySERVERName;database=Testing;trusted_connection=true;')

for (i in 1:nrow(f)) {
  sqlQuery(dbhandle, paste0("EXEC BOinsert @age=", f[i,]$age, ", @name='", f[i,]$name, "'"))
  # print(paste0("EXEC BOinsert @age=", f[i,]$age, ", @name='", f[i,]$name, "'"))
}

odbcClose(dbhandle)

Answer 4

Alternative 2 of my first answer (create a block of SQL statements that insert many rows at once) could be implemented like this:

library(RODBC)

f <- data.frame(age = c(21,22,23,24), name = c("fifi", "jojo", "jj", "arbi"), stringsAsFactors = FALSE)

dbhandle <- odbcDriverConnect('driver={SQL Server};server=MySERVERName;database=Testing;trusted_connection=true;')

block.size.in.rows <- 2  # you should use a much bigger value like 100 (2 is only for demonstration purposes here)
sql <- ''

for (i in 1:nrow(f)) {
  # sqlQuery(dbhandle, paste0("EXEC BOinsert @age=", f[i,]$age, ", @name='", f[i,]$name, "'"))
  sql <- paste0(sql, "EXEC BOinsert @age=", f[i,]$age, ", @name='", f[i,]$name, "'", "; \n")
  if ((i %% block.size.in.rows) == 0) {
    cat(paste0("Sending block:\n"))
    cat(sql)      # debug
    sqlQuery(dbhandle, sql)
    sql <- ''
  }
}

if (nchar(sql) > 0) {
  cat(paste0("Sending block:\n"))
  cat(sql)      # debug
  sqlQuery(dbhandle, sql)
}

odbcClose(dbhandle)

It will send multiple SQL statements in a block via one server roundtrip:

Sending block:
EXEC BOinsert @age=21, @name='fifi'; 
EXEC BOinsert @age=22, @name='jojo'; 
Sending block:
EXEC BOinsert @age=23, @name='jj'; 
EXEC BOinsert @age=24, @name='arbi';