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?

Rowboat

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. – Sodden

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

Sodden

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. – Rowboat

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. – Sodden

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? – Amelita

There's no naming of the target table columns in the pasted result, so the order would have to be correct. – Sodden

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

Broaden

dbWriteTable is much faster than RODBC::sqlSave – Ripsaw

Answer 3

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)

Ellene

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? – Lolanthe

@MihaTrošt Do you manage to work with other functions of rsqlserver' ? better to create an issue in rsqlserver issue, I will look at it closely. – Ellene

I can work with other functions and also, following this topic #19191244, 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. – Lolanthe

@MihaTrošt your files have to be on a drive that the Database server can see to be able to do a bulk load. – Zack

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. – Ostensible