无法在R中使用sqlSave附加到SQL Server表 [英] Unable to append to SQL Server table using sqlSave in R

问题描述

我正在尝试使用R中的RODBC包的sqlSave函数更新SQL表. 数据存在于数据帧中. 当我尝试运行命令时:

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)

我收到以下错误:

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 ( ?,?,?,?,? )'

在这里我在做什么错，以至于我无法在SQLQuery中获取值?

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

非常感谢您提前提供帮助

Thanks for any help in advance

编辑1 :

错误显示5个占位符，但我的data.frame只有4列.我做了dim(df)并得到了4.它与df的行索引有关系吗?

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?

编辑2

执行以下操作:

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

现在我得到的错误仍然是4个占位符，但是所有值仍然是(?,?,?,?)

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

编辑3

我也尝试使用sqlUpdate

I tried using sqlUpdate also

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

我现在遇到的错误是:

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

推荐答案

数据类型和列名称可能会出现问题. 因此，最好获取表的数据类型和列名，并将它们分配给数据框.

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 )

这篇关于无法在R中使用sqlSave附加到SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！