I am using RODBC package in R to import / export data frames from SQL Server database. While there is no problem in importing. I dont know how to export the contents of a data frame into an existing SQL table.
I am trying to use sqlQuery() function available in the package, but I am not sure how to insert multiple records in the table.
A sample on how to insert the rows will be helpful I have ensured that columns of my table and data frame are same.
Use sqlSave with append property. See my code below:
sqlSave(uploaddbconnection, outputframe, tablename =
"your_TableName",rownames=FALSE, append = TRUE)
This is my code on using sqlSave(). I am using SQL Server 2008. Conn is a connection that I created using odbcConnect():
#creating data to be saved in SQL Table
data_to_save<-cbind(scenario_1,scenario_2,scenario_3,scenario_4,store_num,future_date,Province,index)
#use sqlSave() rather than sqlQuery() for saving data into SQL Server
sqlSave(conn,data.frame(data_to_save),"CC_Forecast",safer=FALSE,append=TRUE)
sqlQuery() is a more general function which inserts data frame row by row, Also sqlSave() doesn't work if your data frame columns are subset of your tabledbWriteTable(conn, "RESULTS", results2000, append = T)
use DBI package
I would like to add upon Yan's answer.
Before you use sqlSave() function, make sure you change your default database to the right database where your table are. Especially if you want to write to an existing table!
See here: for how to set up ODBC connection and how to change default database.
After that, you can use these to dump data to sql:
specificDB= odbcConnect(dsn ='name you set up in ODBC',uid = '***', pwd = '****')
sqlSave(specificDB, output_to_sql, tablename = 'a_table_in_specificDB', rownames = F,append = T)
close(specificDB)
It is slow. be patient.
