Using INSERT statement in sqldf within R

Question

I am trying to use sqldf to insert rows into an existing (but empty) dataframe and I am getting a strange error.

Here is my code:

set.seed(1)

library(sqldf)

# define empty dataframe
d1 = data.frame(min_x=numeric(), max_x=numeric())

# create a data frame of random numbers
d2 = data.frame(x=runif(10))

# This works fine
sqldf("select min(x), max(x) from d2")

# But, the following statement generates an error 
sqldf("insert into d1 select min(x), max(x) from d2")

Here is the output:

Not sure how to fix this. I am running R 3.1.0 on a 64-bit machine using Windows 7.

>     set.seed(1)
>     
>     library(sqldf)
>     
>     # define empty dataframe
>     d1 = data.frame(min_x=numeric(), max_x=numeric())
>     
>     # create a data frame of random numbers
>     d2 = data.frame(x=runif(10))
>     
>     # This works fine
>     sqldf("select min(x), max(x) from d2")
      min(x)    max(x)
1 0.06178627 0.9446753
>     
>     # But, the following statement generates an error 
>     sqldf("insert into d1 select min(x), max(x) from d2")
Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such table: d1)
In addition: Warning message:
In value[[3L]](cond) : bind.data must have non-zero dimensions
> 

What am I doing wrong? Thanks in advance.

Answer 1

1) As indicated in the warning it can't deal with zero dimensioned data frames so if you really want to do this then try the following:

# before running this be sure that data frame d1 does NOT exist

sqldf(c("create table d1(min_x real, max_x real)", 
        "insert into d1 select min(x), max(x) from d2", 
        "select * from d1"))

This seems to be a problem with the RSQLite driver that sqldf uses and if you feel this should be possible then it should be fixed in that package.

2) If you are willing to switch databases then you can do it. The RH2 driver for the H2 database does not have this problem. and we can tell sqlidf to use it by simply loading RH2 prior to issuing the sqldf statement - if sqldf notices that RH2 is loaded then it assumes you wanted to use that instead of SQLite.

library(RH2)

Then issue the code in the question except replace the last line with:

sqldf(c("insert into d1 select min(x), max(x) from d2", "select * from d1"))

since the code in the question did not return anything.