Batch / bulk insert in R

Question

I am trying to do a batch insert in R using RJDBC. It seems to insert 1 row at a time, which takes a lot of time.

I was wondering if anyone knew of a solution in R to do bulk inserts of data from R to SQL. I know that RODBC can perform parameterized insertion, which is fast, but not as fast as bulk insertion.

Answer 1

I do not know about your language "R", but sqlExe has a SQL statement BULK.

sqlExe is a utility that connects to SQL databases via ODBC and executes any valid SQL, plus it has some additional functions ( http://sourceforge.net/projects/sqlexe/ )

For example, assuming the target table is:

table: [mydata] ------------------- row_id char(1) row_idx integer row_desc char(32) 

To complete the insert task using sqlExe, you must prepare a file with your input:

  input.dat a,1,this is row 1 b,2,this is row 2 c,3,this is row 3 d,4,this is row 4 

Command line for import:

  sql --dsn MYDB -e "BULK INSERT input.dat, INSERT INTO mydata(row_id,row_idx,row_desc) VALUES(?,?,?)"