Unable to append to SQL Server table using sqlSave in R

user1465557 picture user1465557 · May 15, 2016 · Viewed 11.4k times · Source

I am trying to update a SQL table using sqlSave function of RODBC package in R. Data is present in a data frame. When I try to run the command:

sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)

I get the following error:

Query: INSERT INTO "table" ( "col1", "col2", "col3", "col4" ) VALUES ( ?,?,?,?,? )
sqlwrite returned
42000 -131 [Sybase][ODBC Driver][Sybase IQ]Syntax error near 'table' on line 1
[RODBC] ERROR: Could not SQLPrepare 'INSERT INTO "table" ( "col1", "col2", "col3", "col4" ) VALUES ( ?,?,?,?,? )'

What am I doing wrong here so that I am not getting the values in SQLQuery?

Thanks for any help in advance

EDIT 1 after @Gordon comment:

Error shows 5 placeholders but my data.frame has only 4 columns. I did dim(df) and got 4. Is it somehow related to row index of df?

EDIT 2

On doing the following:

sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)

The error now I get is still the same with 4 placeholders instead but all values are still (?,?,?,?)

EDIT 3

I tried using sqlUpdate also

sqlUpdate(DBConn, dat=df, verbose=T, tablename='table')

Error that I now got is:

Query: UPDATE "table" SET "col2"=?, "col3"=?, "col4"=? WHERE "col1"=?
Error in sqlUpdate(DBConn, t, tablename = "table", verbose = T) :
  42000 -131 [Sybase][ODBC Driver][Sybase IQ]Syntax error near 'table' on line 1[RODBC] ERROR: Could not SQLPrepare 'UPDATE "table" SET "col2"=?, "col3"=?, "col4"=? WHERE "col1"=?'

Answer

Linda picture Linda · Aug 31, 2016

There is a possibility of data types and Column names being a problem. So It's best to obtain the datatypes and column names of the table and assign them to the data frame.

ColumnsOfTable       <- sqlColumns(conn, tablename)
varTypes             <- as.character(ColumnsOfTable$TYPE_NAME) 
names(varTypes)      <- as.character(ColumnsOfTable$COLUMN_NAME) 
colnames(dataObject) <- as.character(ColumnsOfTable$COLUMN_NAME)

sqlSave(conn, dataObject, tableNames, fast=TRUE,append=TRUE,  rownames=FALSE, varTypes=varTypes )