Refresh SQL table through R sqlSave

Question

I have a data frame in R having 3 columns using sqlSave. I can easily create a table in an SQL database:

channel <- odbcConnect("JWPMICOMP") sqlSave(channel, dbdata, tablename = "ManagerNav", rownames = FALSE, append = TRUE, varTypes = c(DateNav = "datetime")) odbcClose(channel) 

This data frame contains information about managers (Name, Nav and Date) that are updated every day with new values ​​for the current date, and perhaps the old values ​​can be updated in case of errors.

How can I complete this task in R?

I try to use sqlUpdate, but it returns me the following error:

 > sqlUpdate(channel, dbdata, tablename = "ManagerNav") Error in sqlUpdate(channel, dbdata, tablename = "ManagerNav") : cannot update 'ManagerNav' without unique column 

Answer 1

When you create a white shark table (see the documentation), it does not receive the primary index, but is simply columns and often of the wrong type. Usually I use your approach to enter the column names correctly, but after that you should go into your database and assign a primary index, fix the column widths and types.

After that, sqlUpdate can work; I can say that since I refused to use sqlUpdate, there are too many caveats and use sqlQuery (..., paste ("Refresh ....))) for the real work.

Answer 2

I would do the following for this

Solution 1

sqlUpdate (channel, dbdata, tablename = "ManagerNav", index = c ("ManagerNav"))

Decision 2

Lcolumns <- list (dbdata [0,]) sqlUpdate (channel, dbdata, tablename = "ManagerNav", index = c (Lcolumns))

A pointer is used to indicate which R columns are about to be updated. Hope this helps!