Unable to append to SQL Server table using sqlSave in R

Question

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"=?'

Mccaffrey

You have four columns and five placeholders, for one thing. – Madiemadigan

@GordonLinoff : Nice observation. But doing dim(t) gives 4 only. Can it because of row inde or something related to it? – Mccaffrey

FWIW I have never been able to get this command to append existing tables correctly; it seems like a bug to me. – Zinazinah

@Zinazinah I tried sqlUpdate also. Command and the error that i got is there under EDIT 3. Can you please have a look at it or suggest a way you prefer to upload a data frame of R to sql server? – Mccaffrey

The only way I have been able to insert data into existing tables is by manually generating the INSERT statements, as demonstrated here. Unfortunately this is not as efficient as leveraging the DB's native bulk insert / prepared statement capabilities, which sqlSave would presumably be doing if it functioned correctly, but it's better than nothing I suppose. – Zinazinah

@Zinazinah Thanks for sharing the code. I can do this but the problem is that I have around 100000 to be inserted and using this is very inefficient as it takes around 3 sec for 100 values. – Mccaffrey

It may be more efficient to first send all of your data to a (non existent) temporary table in SQL and then run a separate statement like SET NOCOUNT ON; INSERT INTO <actual target table> (<columns...>) SELECT * FROM <temporary table>; SET NOCOUNT OFF; DROP TABLE <temporary table>;, or something of that nature. I haven't tested this but I would imagine it to be faster than individual inserts. – Zinazinah

@Zinazinah Correct me if I am wrong but sending to a temporary table will still have a call to 100000 INSERT INTO statements which will again take the same time. How will it be more efficient inserting into a temporary table considering the query used in this case will again be same? – Mccaffrey

Assuming that sqlSave is written properly (WRT creating new tables), it should use prepared statements to create / populate the temporary table, and not execute individual inserts. I don't have access to a DB at the moment, but you would do something like sqlSave(DBConn, dat = df, tablename = '#temp_table', append = FALSE) to upload the data, and then proceed as described in my previous comment. – Zinazinah

Does data frame contain exactly 4 columns? If not subset the columns. Check also if data types align. Also, is the table name really table? If so consider renaming as it is a reserved keyword in SQL Server. – Incarnation

Answer 1

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 )

Shadowy

Answer 2

The reason I had this problem is: I was trying to append to a table that had an auto-incrementing identity column. If I omitted this column from the data frame it would give me this error missing columns in 'data'. If I made this column NA it would give me Invalid character value for cast specification I was able to troubleshoot with verbose=TRUE. To solve, create a VIEW from the table that has all the columns except the primary key so you can append to this VIEW instead of the table, then you do not need to append the primary key. in my case, the view is called "insert_view"

var_Types <- as.character(as.character(c("int","int","varchar(50)","nvarchar(MAX)")))

names(var_Types) <- as.character(ColumnsOfTable$COLUMN_NAME)

sqlSave(ch, dataframe, "dbo.insert_view",rownames=FALSE,append = TRUE,varTypes=var_Types,verbose = TRUE)

Danford

Answer 3

Check using verbose=TRUE in sqlSave argument if any of the columnames in the insert query has the same that you have in your original table.

In my table I used to have a columname with space (the same if it has numbers o different character). It won´t work because sqlSave will remove those character when it creates the query.

Yiyid