无法在 R 中使用 sqlSave 追加到 SQL Server 表
•浏览 1
Unable to append to SQL Server table using sqlSave in R
我正在尝试使用 R 中 RODBC 包的 sqlSave 函数更新 SQL 表。
数据存在于数据框中。
当我尝试运行命令时：
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 ( ?,?,?,?,? )'
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
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"=?'
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 )我收到以下错误：
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 ( ?,?,?,?,? )'
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
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"=?'
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 )我在这里做错了什么，所以我没有得到 SQLQuery 中的值？
提前感谢您的帮助
@Gordon 评论后编辑 1：
错误显示 5 个占位符，但我的 data.frame 只有 4 列。我做了 dim(df) 并得到 4。它是否与 df 的行索引有关？
编辑 2
关于执行以下操作：
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 ( ?,?,?,?,? )'
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
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"=?'
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 )现在我得到的错误仍然与 4 个占位符相同，但所有值仍然是 (?,?,?,?)
编辑 3
我也尝试使用 sqlUpdate
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 ( ?,?,?,?,? )'
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
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"=?'
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 )我现在得到的错误是：
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 ( ?,?,?,?,? )'
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
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"=?'
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 )数据类型和列名有可能是个问题。
所以最好获取表的数据类型和列名，并分配给数据框。
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 ( ?,?,?,?,? )'
sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T)
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"=?'
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 )我遇到此问题的原因是：我试图追加到具有自动递增标识列的表。如果我从数据框中省略了这一列，它会给我这个错误 missing columns in 'data'。如果我将此列设为 NA，它将给我 Invalid character value for cast specification，我能够使用 verbose=TRUE 进行故障排除。要解决此问题，请从包含除主键之外的所有列的表中创建一个 VIEW，以便您可以附加到此 VIEW 而不是表，然后您不需要附加主键。就我而言，该视图称为"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)
如果插入查询中的任何列名与原始表中的列名相同，请在 sqlSave 参数中使用 verbose=TRUE 检查。
在我的表中，我曾经有一个带空格的列名(如果它有数字或不同的字符，则相同)。它不会起作用，因为 sqlSave 在创建查询时会删除这些字符。