R RODBC sqlUpdate or sqlQuery

Question

I am trying to insert a dataframe into a sql table. The sqlUpdate code gives a inherits(channel, "RODBC") channel is missing error Insertdata<-sqlUpdate(DBcon, df, tablename = "DataQuality", sqlColumns(special=FALSE)) My second attempt was this

Insertdata<-sqlQuery(DBcon,paste("insert into DataQuality", df, sep = ""))

sqlQuery shows an error of

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Nothing to add'

." (this is the first column and row of data)

"[RODBC] ERROR: Could not SQLExecDirect 'insert into DataQuality(Descriptions,Date,Name,Ref)SELECT Descriptions,Date,Name,Ref FROMc(\"Nothing to add\"

, and seems to stop at the end of the first column.

Did you try to use sqlSave? Per the docs: sqlSave saves the data frame dat in the table tablename. If the table exists and has the appro- priate structure it is used, or else it is created anew. — Parfait
– Parfait, Commented May 18, 2017 at 14:28
I thought sqlSave was for creating a new table I am not sure how I would use this to insert into a table? when I try Insertdata<-sqlSave( DBcon, df, "DataQuality") I get the error saying the table already exists. — New2Programming
– New2Programming, Commented May 18, 2017 at 15:03
I tried using append=TRUE but it crashes R. I have seen this post onSqlSave. — New2Programming
– New2Programming, Commented May 18, 2017 at 15:58
I managed to find out why, I needed rownames=FALSE. my final code that worked was Insertdata<-sqlSave( DBcon, df, "DataQuality",rownames=FALSE, append=TRUE)Thank you for the help. — New2Programming
– New2Programming, Commented May 18, 2017 at 16:08

New2Programming · Accepted Answer · 2017-05-18 16:20:17Z

1

Insertdata<-sqlSave( DBcon, df, "DataQuality",rownames=FALSE, append=TRUE) This will update the SQL table with the new dataframe.

answered May 18, 2017 at 16:20

New2Programming

3831 gold badge4 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

R RODBC sqlUpdate or sqlQuery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related