sqlSave error: table not found

Question

I'm trying to save a R dataframe back to a sql database with the following code:

    channel <- odbcConnect("db")
    sqlSave(db, new_data, '[mydb].[dbo].mytable', fast=T, rownames=F, append=TRUE)

However, this returns the error "table not found on channel", while simultaneously creating an empty table with column names. Rerunning the code returns the error "There is already an object named 'mytable' in the database". This continues in a loop - can someone spot the error?

Answer 1

Is this about what your data set looks like?

MemberNum  x             t.x T.cal m.x T.star h.x h.m.x e.trans e.spend       
1          2.910165e+12  0   0     205 8.77   52  0     0       0.0449161  

I've had this exact problem a few times. It has nothing to do with a table not being found on the channel. From my experience, sqlSave has trouble with dates and scientific notation. Try converting x to a factor:

new_data$x = as.factor(new_data$x)

and then sqlSave. If that doesn't work, try as.numeric and even as.character (even though this isn't the format that you want.