5

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"=?'
user1465557
  • 329
  • 2
  • 4
  • 11
  • 2
    You have four columns and five placeholders, for one thing. – Gordon Linoff May 15 '16 at 16:06
  • @GordonLinoff : Nice observation. But doing dim(t) gives 4 only. Can it because of row inde or something related to it? – user1465557 May 15 '16 at 16:11
  • FWIW I have never been able to get this command to append existing tables correctly; it seems like a bug to me. – nrussell May 15 '16 at 16:28
  • @nrussell 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? – user1465557 May 15 '16 at 16:58
  • The only way I have been able to insert data into existing tables is by manually generating the `INSERT` statements, [as demonstrated here](http://stackoverflow.com/questions/37117960/r-create-temporary-table-in-sql-server-from-r-data-frame/37119981#37119981). 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. – nrussell May 15 '16 at 17:06
  • @nrussell 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. – user1465557 May 15 '16 at 17:09
  • 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 () SELECT * FROM ; SET NOCOUNT OFF; DROP TABLE ;`, or something of that nature. I haven't tested this but I would imagine it to be faster than individual inserts. – nrussell May 15 '16 at 17:18
  • @nrussell 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? – user1465557 May 15 '16 at 17:24
  • 1
    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. – nrussell May 15 '16 at 17:28
  • 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](https://msdn.microsoft.com/en-us/library/ms189822.aspx). – Parfait May 16 '16 at 02:00

3 Answers3

17

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 )
Hack-R
  • 22,422
  • 14
  • 75
  • 131
Linda
  • 627
  • 4
  • 14
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)

Michael Larsen
  • 21
  • 2
0

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.

Eve
  • 11