代码之家 › 专栏 › 技术社区 › Chase

RODBC sqlSave()在违反PK时停止插入查询

rodbc r sql-server-2005

Chase · 技术社区 · 15 年前

我开发了一个在线调查,将我的数据存储在一个Microsoft SQL 2005数据库中。我在R中对数据编写了一组异常值检查。这些脚本的一般工作流是:

使用sqlQuery()从SQL数据库读取数据
执行异常值分析
使用sqlSave()将有问题的响应者写回单独表中的数据库

我要回写的表具有以下结构:

CREATE TABLE outliers2(
    modelid int
    , password varchar(50)
    , reason varchar(50),
Constraint PK_outliers2 PRIMARY KEY(modelid, reason)
)
GO

如您所见,我已将主键设置为modelid和reason。对于多个检查,同一个响应者可能是异常值,但我不想为任何响应者插入相同的modelid和reason组合。

由于我们仍在收集数据,因此我希望在开发基于数据估计的模型时能够每天/每周更新这些脚本。以下是 sqlSave() 我使用的命令:

sqlSave(db, db.insert, "outliers2", append = TRUE, fast = FALSE, rownames = FALSE)

哪里 db 是有效的ODBC连接,并且 db.insert 有表格

> head(db.insert)
  modelid password          reason
1     873       abkd WRONG DIRECTION
2     875       ab9d WRONG DIRECTION
3     890       akdw WRONG DIRECTION
4     905       pqjd WRONG DIRECTION
5     941       ymne WRONG DIRECTION
6     944       okyt WRONG DIRECTION

sqlSave() 当它试图插入违反主键约束且不继续插入其他记录的行时阻塞。我本以为那个场景 fast = FALSE 本来可以缓解这个问题,但没有。

关于如何解决这个问题有什么想法吗?我可以永远 drop 第一个剧本开头的那张桌子,看起来很重,无疑会导致问题的产生。

1 回复 | 直到 15 年前

Choens 15 年前

在这种情况下,一切都按预期进行。您将所有内容作为批处理上载,而SQL Server将在发现错误时立即停止批处理。不幸的是,我不知道一个优雅的内置解决方案。但是,我认为可以在数据库中建立一个系统来更有效地处理这个问题。我喜欢在数据库中进行数据存储/管理,而不是在R中,因此我的解决方案非常繁重。其他人可能会给你提供一个更面向R的解决方案。

首先,创建一个不带约束的简单表来保存新行并相应地调整sqlSave语句。这是R将信息上载到的位置。

CREATE TABLE tblTemp(
    modelid int
    , password varchar(50)
    , reason varchar(50)
    , duplicate int()
)
GO

将信息放入此表的查询应假定列“duplicate”为“No”。我使用的模式是1=Y&5=N。你也只能标记那些异常值,但我倾向于用我的逻辑来明确。

您还需要一个地方来转储所有在异常值2中违反PK的行。

CREATE TABLE tblDuplicates(
    modelid int
    , password varchar(50)
    , reason varchar(50)
)
GO

好啊。现在您只需要创建一个触发器,将新行从tblTemp移动到outliers2。此触发器将把所有重复行移动到tblDuplicates,以便以后处理、删除或其他操作。

CREATE TRIGGER FindDups
ON tblOutliersTemp
AFTER INSERT
AS

我不会把整个触发器都写下来。我没有一个SQL Server 2005来测试它,我可能会犯语法错误,我不想给你错误的代码,但触发器需要做的是:

标识tblTemp中所有与异常值2中的PK冲突的行。如果找到重复项,请将重复项更改为1。这将通过UPDATE语句完成。
将duplicate=1的所有行复制到tblDuplicates。你可以在tblDuplicates中插入。。。。。。
现在,使用INSERT-in to语句将非重复行复制到outliers2,该语句看起来几乎与步骤2中使用的语句完全相同。
删除tblTemp中的所有行,以便为下一批更新清除它。这一步很重要。

这样做的好处是sqlSave()不会因为PK有冲突而出错,您可以在以后的时间处理匹配,比如明天。:-)