How to insert a dataframe into a SQL Server table?

Question

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?

Answer 1

[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") 

Answer 2

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