24

I'm trying to upload a dataframe to a SQL Server table, I tried breaking it down to a simple SQL query string..

library(RODBC)
con <- odbcDriverConnect("driver=SQL Server; server=database")

df <- data.frame(a=1:10, b=10:1, c=11:20)

values <- paste("(",df$a,",", df$b,",",df$c,")", sep="", collapse=",")

cmd <- paste("insert into MyTable values ", values)

result <- sqlQuery(con, cmd, as.is=TRUE)

..which seems to work but does not scale very well. Is there an easier way?

jenswirf
  • 7,087
  • 11
  • 45
  • 65
  • 1
    If the table exists then "append" needs to be TRUE, or use sqlUpdate. If it doesn't exist I would have stuck with the default (FALSE) in sqlSave. I have read that there are weird naming conventions for SQL Server but I do not have a copy so unable to test. – IRTFM Jan 15 '13 at 19:08

3 Answers3

21

[edited] Perhaps pasting the names(df) would solve the scaling problem:

   values <- paste( " df[  , c(", 
                     paste( names(df),collapse=",") ,
                                   ")] ", collapse="" ) 
      values
      #[1] " df[  , c( a,b,c )] "

You say your code is "working".. I would also have thought one would use sqlSave rather than sqlQuery if one wanted to "upload".

I would have guessed this would be more likely to do what you described:

 sqlSave(con, df, tablename = "MyTable")
IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • Not sure what you're trying to do with the `values` variable, that just creates a wierd looking string?! You are right about the trailing comma, that would be a problem. The "extra quotes" are left from my original code. – jenswirf Jan 15 '13 at 12:32
  • That was just supposed to illustrate that `names(df)` and "[" could be used to access a data.frame by columns more abstractly than what you were doing. It would need a `collapse=""` or `collapse=","` to be effective. – IRTFM Jan 15 '13 at 17:56
  • Assuming the column names of dataframe and SQL table are same, does this insert the data in the respective column of the table as in dataframe? Or do I need to keep the sequence of dataframe columns same as the table? – Aashish Gulabani Jun 20 '18 at 13:36
  • There's no naming of the target table columns in the pasted result, so the order would have to be correct. – IRTFM Jun 20 '18 at 20:31
8

This worked for me and I found it to be simpler. 

library(sqldf)
library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "ServerName",
                 Database = "DBName",
                 UID = "UserName",
                 PWD = "Password")
dbWriteTable(conn = con, 
             name = "TableName", 
             value = x)  ## x is any data frame
Shreyas K
  • 106
  • 1
  • 2
6

Since insert INTO is limited to 1000 rows, you can dbBulkCopy from rsqlserver package.

dbBulkCopy is a DBI extension that interfaces the Microsoft SQL Server popular command-line utility named bcp to quickly bulk copying large files into table. For example:

url = "Server=localhost;Database=TEST_RSQLSERVER;Trusted_Connection=True;"
conn <- dbConnect('SqlServer',url=url)
## I assume the table already exist
dbBulkCopy(conn,name='T_BULKCOPY',value=df,overwrite=TRUE)
dbDisconnect(conn)
agstudy
  • 119,832
  • 17
  • 199
  • 261
  • I cannot get dbBulkCopy to work...it produces a strange error like this...Error in clrCallStatic("rsqlserver.net.misc",...and goes on... Type: System.Data.SqlClient.SqlException...I do not even know if it is dbBulkCopy error or rClr package error...Have you got any idea what could it be? – Miha Trošt Apr 02 '15 at 07:54
  • 1
    @MihaTrošt Do you manage to work with other functions of `rsqlserver'` ? better to create an issue in [rsqlserver issue](/issues), I will look at it closely. – agstudy Apr 03 '15 at 03:13
  • 1
    I can work with other functions and also, following this topic http://stackoverflow.com/questions/19190744/how-to-quickly-export-data-from-r-to-sql-server, I think that it is not dbBulkCopy issue...first, I did not have bulk insert permissions (I have now), second, the file I tried to dbBulkCopy was not on machine where sql server is installed and it could not find it. So...for now, please never mind my comments and thank you for your time. – Miha Trošt Apr 03 '15 at 07:57
  • @MihaTrošt your files have to be on a drive that the Database server can see to be able to do a bulk load. – dreyco676 Sep 18 '15 at 19:17
  • Is `rsqlserver` viable? It's not frequently updated and has pretty heavy dependencies. The code seems pretty iffy -- look at how NA is handled in `insert.into`, for example. – MikeB Aug 17 '23 at 02:26