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?

Transmit

If you post the results of head(new_data), I can probably tell you what's wrong. – Coppinger

@Coppinger head(new_data) returns: MemberNum x t.x T.cal m.x T.star h.x h.m.x e.trans e.spend lost 1 2.910165e+12 0 0 205 8.77 52 0 0 0.0449161 39.27263 0.7983145 alive a.value e.value 1 0.2016855 0 1.763974........Not sure how to post this in a comment that makes it more readable. – Transmit

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.

Coppinger

Same problem with Integer. You'd think that Integer would be an easy data type to get right. Sadly this solution didn't work for that. – Primogeniture

Answer 2

As a first shot try to run sqlTables(db) to check the tables in the db and their correct names. You could then potentially use this functions return values as the input to sqlSave(...)

Navarino

It shows table is available but sqlSave() says object not found – Breuer

@Breuer sqlsave and sqlupdate are horrible implementation. I am struggling to get it right. – Malayan

Answer 3

It seems you are trying to write to a SQL Server. If you specify the database name in the ODBC connection, and then refer to the table as "dbo.mytable" it might help.

Northwester

Answer 4

I could do it changing the connection in the driver odbc. When you open it, you can do it for one db or in general for all dbs. When you opened it for one db, you will not have a problem with sqlSave().

Quinze