CODEWITHSUNDEEP
sqlrrodbc
ephpostfacto
ephpostfacto

Reputation: 303

MS-SQL Bulk Insert with RODBC

Is it possible to perform a bulk insert into an MS-SQL Server (2000, 2005, 2008) using the RODBC package?

I know that I can do this using freebcp, but I'm curious if the RODBC package implements this portion of the Microsoft SQL API and if not, how difficult it would be to implement it.

Upvotes: 8

Views: 11750

Answers (6)

quickreaction
quickreaction

Reputation: 685

Using RODBC, the fastest insert we've been able to create (260 million row insert) looks like the following (in R pseudo code):

ourDataFrame <- sqlQuery(OurConnection, "SELECT myDataThing1, myDataThing2
                                         FROM myData")
ourDF <- doStuff(ourDataFrame)
write.csv(ourDF,ourFile)  
sqlQuery(OurConnection, "CREATE TABLE myTable ( la [La], laLa [LaLa]);
                         BULK INSERT myTable FROM 'ourFile' 
                              WITH YOURPARAMS=yourParams;")

If you're running this from between servers, you need a network drive that the R server can write to (e.g. one server with permissions for writing to the DB uses Rscript to productionalize the code), and the SQL Server can read from.

Upvotes: 2

quickreaction
quickreaction

Reputation: 685

check out the new odbc and DBI packages. DBI::dbWriteTable writes around 20,000 records per second... Much much faster than the Row Inserts from RODBC::sqlSave()

Upvotes: 6

Thierry
Thierry

Reputation: 18487

Our n2khelper package can use bcp (bulkcopy) when it is available. When not available it falls back to multiple INSERT statements.

You can find the package on https://github.com/INBO-Natura2000/n2khelper

Install it with devtools::install_git("INBO-Natura2000/n2khelper") and look for the odbc_insert() function.

Upvotes: 1

quickreaction
quickreaction

Reputation: 685

From everything I can find, there is NO solution for bulk insert to MySQL and nothing that works with SSIS which is why Microsoft is including in-database analytics with SQL Server 2016 after buying Revolution R Analytics.

I tried to comment on the previous answer but don't have the reputation to do it.

The rsqlserver package needs to run with rClr and neither of those packages are well-behaved, especially because rsqlserver's INSERT functions have poor data type handling. So if you use it, you'll have no idea what you're looking at in the SQL table as much of the information in your data.frame will have been transformed.

Considering the RODBC package has been around for 15 years, I'm pretty disappointed that no one has created a bulk insert function...

Upvotes: 1

agstudy
agstudy

Reputation: 121568

Now You can use dbBulkCopy from the new rsqlserver package:

A typical scenario:

  1. You create a matrix
  2. you save it as a csv file
  3. You call dbBulkCopy to read fil and insert it using internally bcp tool of MS Sql server.

This assume that your table is already created in the data base:

dat <- matrix(round(rnorm(nrow*ncol),nrow,ncol)
id.file = "temp_file.csv"                      
write.csv(dat,file=id.file,row.names=FALSE)
dbBulkCopy(conn,'NEW_BP_TABLE',value=id.file)

Upvotes: 2

Tyler
Tyler

Reputation: 626

You're probably looking for ?sqlSave which uses a parametrized INSERT INTO query (taking place in one operation) when you set Fast=True.

Upvotes: 2

Related Questions