如何使用RODBC将DataFrame存入带数据库生成主键的表

嘿，我之前也碰到过这个RODBC的坑！当目标表有自增IDENTITY主键时，sqlSave确实会因为列匹配的问题报错，咱们一步步来解决它。

问题回顾 #

你遇到的错误：

Error in dimnames(x) <- dn : length of 'dimnames' [2] not equal to array extent

本质原因是你的DataFrame里没有ID列（毕竟这个是数据库自动生成的），但sqlSave默认会尝试对齐数据库表的所有列，列数不匹配就触发了这个维度错误。而且因为目标表已有数据，咱们没法在R里生成主键再追加，得换个思路。

先确认下你的目标表结构和修正后的测试代码（我帮你改了测试代码里的一个小笔误：ConnectionString应该是ConnectionString2）：

目标表SQL #

CREATE TABLE [dbo].[results] ( 
    [ID] INT IDENTITY (1, 1) NOT NULL, 
    [FirstName] VARCHAR (255) NULL, 
    [LastName] VARCHAR (255) NULL, 
    [Birthday] DATETIME NULL, 
    [CreateDate] DATETIME NULL, 
    CONSTRAINT [PK_dbo.results] PRIMARY KEY CLUSTERED ([ID] ASC) 
);

修正后的测试R代码 #

ConnectionString1="Driver=ODBC Driver 11 for SQL Server;Server=myserver; Database=TestDb; trusted_connection=yes"
ConnectionString2="Driver=ODBC Driver 11 for SQL Server;Server=notmyserver; Database=TestDb; trusted_connection=yes"

db1=odbcDriverConnect(ConnectionString1)
query="SELECT a.[firstname] as FirstName 
       , a.[lastname] as LastName 
       , Cast(a.[dob] as datetime) as Birthday 
       , cast(a.createDate as datetime) as CreateDate 
       FROM [dbo].[People] a"
results=NULL
results=sqlQuery(db1,query,stringsAsFactors=FALSE)
close(db1)

db2=odbcDriverConnect(ConnectionString2)
# 原sqlSave语句在这里报错
close(db2)

可行解决方案 #

给你三种方法，按可靠性和易用性排序：

方法1：直接构造INSERT语句（最稳妥） #

绕开sqlSave，手动写插入语句，只插入DataFrame里有的列，让数据库自己生成ID。这样完全可控，不会有列匹配问题：

# 连接到目标数据库后执行这段代码
# 构造批量INSERT语句
insert_rows <- apply(results, 1, function(row) {
  # 处理字符串转义（单引号换成两个单引号）和空值
  fn <- ifelse(is.na(row["FirstName"]), "NULL", paste0("'", gsub("'", "''", row["FirstName"]), "'"))
  ln <- ifelse(is.na(row["LastName"]), "NULL", paste0("'", gsub("'", "''", row["LastName"]), "'"))
  # 日期格式转成SQL Server能识别的格式
  bd <- ifelse(is.na(row["Birthday"]), "NULL", paste0("'", format(as.POSIXct(row["Birthday"]), "%Y-%m-%d %H:%M:%S"), "'"))
  cd <- ifelse(is.na(row["CreateDate"]), "NULL", paste0("'", format(as.POSIXct(row["CreateDate"]), "%Y-%m-%d %H:%M:%S"), "'"))
  paste0("(", fn, ",", ln, ",", bd, ",", cd, ")")
})

insert_query <- paste0(
  "INSERT INTO [dbo].[results] (FirstName, LastName, Birthday, CreateDate) VALUES ",
  paste(insert_rows, collapse = ", ")
)

# 执行插入
sqlQuery(db2, insert_query)

这个方法要注意处理特殊字符（比如单引号）和空值，避免SQL语法错误，适合数据量不是特别大的场景。

方法2：调整sqlSave参数（勉强能用，但不稳定） #

如果你坚持要用sqlSave，可以通过精确指定列参数来规避问题，核心是告诉函数不要管主键列：

sqlSave(db2, results, append = TRUE, 
        # 明确指定每个列的SQL类型，对应表中除ID外的列
        varTypes=c(FirstName="VARCHAR(255)", LastName="VARCHAR(255)", Birthday="datetime", CreateDate="datetime"),
        colnames = TRUE, # 让函数按列名匹配，而不是位置
        rownames = FALSE,
        fast=FALSE,
        addPK = FALSE) # 关键：禁止函数尝试添加主键

不过这个方法有时候还是会因为RODBC对IDENTITY列的奇怪处理逻辑报错，不如方法1可靠。

方法3：换用DBI+odbc包（长期推荐） #

RODBC是比较老的包了，现在更推荐用DBI和odbc这对组合，它们对SQL Server的自增主键支持非常友好，代码也更简洁：

library(DBI)
library(odbc)

# 建立连接
con <- dbConnect(odbc(), 
                 Driver = "ODBC Driver 11 for SQL Server",
                 Server = "notmyserver",
                 Database = "TestDb",
                 Trusted_Connection = "yes")

# 直接批量追加，自动跳过自增ID列
dbAppendTable(con, "results", results)

# 关闭连接
dbDisconnect(con)

dbAppendTable会自动识别表中的IDENTITY列，不需要手动处理列匹配，是长期维护代码的首选方案。

内容的提问来源于stack exchange，提问作者Matthew