update an SQL table via R sqlSave

sql r sql-update rodbc
Lorenzo Rigamonti picture Lorenzo Rigamonti · Apr 23, 2013 · Viewed 15.3k times · Source

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) which are updatede every day with new values for the current date and maybe old values could be updated too in case of errors.

How can I accomplish this task in R?

I treid 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

Dieter Menne picture Dieter Menne · Apr 23, 2013

When you create a table "the white shark-way" (see documentation), it does not get a primary index, but is just plain columns, and often of the wrong type. Usually, I use your approach to get the columns names right, but after that you should go into your database and assign a primary index, correct column widths and types.

After that, sqlUpdate() might work; I say might, because I have given up using sqlUpdate(), there are too many caveats, and use sqlQuery(..., paste("Update....))) for the real work.

Related questions

How to quickly export data from R to SQL Server

The standard RODBC package's sqlSave function even as a single INSERT statement (parameter fast = TRUE) is terribly slow for large amounts of data due to non-minimal loading. How would I write data to my SQL server with minimal logging so …

Read More
Pass R variable to RODBC's sqlQuery?

Is there any way to pass a variable defined within R to the sqlQuery function within the RODBC package? Specifically, I need to pass such a variable to either a scalar/table-valued function, a stored procedure, and/or perhaps the …

Read More
dplyr left_join by less than, greater than condition

This question is somewhat related to issues Efficiently merging two data frames on a non-trivial criteria and Checking if date is between two dates in r. And the one I have posted here requesting if the feature exist: GitHub issue …

Read More