How to Insert Data to SQL Server Table Using R

How to insert a dataframe into a SQL Server table?

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

How to insert data to SQL Server table using R?

You can write all the data (all rows) using sqlSave(channel, data, rownames = FALSE) where channel <- odbcDriverConnect("Driver={SQL Server};Server=AAA;Uid=BBB;Pwd=CCC;"). This will create a table with a name data in your database.

You can then append your existing table by sqlQuery(channel, 'insert into table select * from data').

Insert R DataFrame into SQL Server Table

Please follow the TIBCO community solution suggested here: https://community.tibco.com/wiki/tibcor-enterprise-runtime-r-fast-writeback-sql-server-2016

In your case it would be something like below:

dbcon <- RODBC::odbcDriverConnect(connection_string )
RODBC::sqlSave(dbcon, dat =  dataf, "SQlServerTableDestinationName")

Please let me know if it helps

How to insert R dataframe into existing table in SQL Server

I've had similar needs using R and PostGreSQL using the r-postgres-specific drivers. I imagine similar issues may exist with SQLServer. The best solution I found was to write to a temporary table in the database using either dbWriteTable or one of the underlying functions to write from a stream to load very large tables (for Postgres, postgresqlCopyInDataframe, for example). The latter usually requires more work in terms of defining and aligning SQL data types and R class types to ensure writing, wheres dbWriteTable tends to be a bit easier. Once written to a temporary table, to then issue an SQL statement to insert into your table as you would within the database environment. Below is an example using high-level DBI library database calls:

  dbExecute(conn,"start transaction;")
  dbExecute(conn,"drop table if exists myTempTable")
  dbWriteTable(conn,"myTempTable",df)
  dbExecute(conn,"insert into myRealTable(a,b,c) select a,b,c from myTempTable")
  dbExecute(conn,"drop table if exists myTempTable")
  dbExecute(conn,"commit;")

How to insert a single row R data.frame into a SQL Server database?

On the assumption that the names and the order of columns in df1 and df2 are identical, this should work:

query <- 
  paste0("INSERT INTO df1 ",
         "(", paste0(names(df2), collapse = ", "), ") ",
         "VALUES (",
         paste0(rep("?", length(df2)), collapse = ", "), ")")

library(RODBCext)

sqlExecute(con, 
           query,
           data = df2)

The query that is written looks like this, when using mtcars as df1.

"INSERT INTO df1 (mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb) VALUES ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?"

This permits you to generate the statement that adds all of column names to the query without having to manually declare them. Using sqlExecute invokes a parameterized query. The question marks are then bound to your data and then executed as part of the statement.

---

Related Topics