如何使用RODBC将数据表保存到数据库生成主键的表中

Question

我想使用R脚本在数据库中的现有表中输入一个数据帧，我希望数据库中的表有一个顺序主键。我的问题是RODBC似乎不允许主键约束。

下面是创建我想要的表的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(ConnectionString)
sqlSave(db2, 
    results, 
    append = TRUE, 
    varTypes=c(Birthday="datetime", CreateDate="datetime"),
    colnames = FALSE,  
    rownames = FALSE,fast=FALSE)
close(db2)

CREATE TABLE [dbo].[results] (
    [FirstName]  VARCHAR (255) NULL,
    [LastName]   VARCHAR (255) NULL,
    [Birthday]   DATETIME      NULL,
    [CreateDate] DATETIME      NULL
);

Answer 1

问题出在http://github.com/cran/rodbc/blob/master/r/sql.r中的第361行--data.frame和DB表必须具有完全相同的列数，否则使用StackTrace会出现以下错误：

Error in dimnames(x) <- dn : 
  length of 'dimnames' [2] not equal to array extent 
3. `colnames<-`(`*tmp*`, value = c("ID", "FirstName", "LastName", 
   "Birthday", "CreateDate")) at sql.R#361
2. sqlwrite(channel, tablename, dat, verbose = verbose, fast = fast, 
   test = test, nastring = nastring) at sql.R#211
1. sqlSave(db2, results, append = TRUE, varTypes = c(Birthday = "datetime", 
    CreateDate = "datetime"), colnames = FALSE, rownames = FALSE, 
    fast = FALSE, verbose = TRUE) 

如果将ID列添加到data.frame中，则不能再使用autoincID列，因此这不是解决方案（或变通方法）。

针对rodbc::sqlsave的“相同列”限制的“简单”解决方案是：

null

一个更好的选择是使用新的ODBC包，该包还通过类似大容量的插入提供更好的性能，而不是发送单个INSERT语句，如RODBCdoes：

https://github.com/r-dbi/odbc

查找函数dbwritetable（它是接口DBI::dbwritetable的实现）。