使用R（DBI包）从SQL Server表中删除行

Question

我在SQL Server中有一张表，现在想要添加数据。在添加数据前，我希望先删除所有现存的记录，但不想删除表格并重新创建，因为它在SQL Server中有索引，我希望保留它们。

使用r语言，我有哪些选择可以实现这个目标？

- ok1more

Answer 1

删除表中所有记录的方法有多种。

您可以使用 TRUNCATE 或 DELETE 命令。

dbExecute(con, "TRUNCATE TABLE TableName")
dbExecute(con, "DELETE FROM TableName")

编辑：请使用dbExecute()代替dbSendQuery()。

如dbSendQuery()的文档中所述：

此方法仅适用于SELECT查询。出于兼容性原因，某些后端可能通过此方法支持数据操作查询。但是，强烈建议调用者使用dbSendStatement()进行数据操作语句。

然而，send方法不会自动清除返回的结果对象。因此，get和execute方法更适合交互式使用。从dbSendStatement()的文档中可以看到：

要查询受影响的行数，请在返回的结果对象上调用dbGetRowsAffected()。之后还必须调用dbClearResult()。对于交互式使用，您几乎总是应该优先选择dbExecute()。

- Kerry Jackson

Answer 2

如果你只需要删除某些记录

回答另一个使用案例，当你需要从数据库中删除仅特定的记录时，可以创建一个查询列表然后使用 map 来执行它们。例如，下面的代码将删除 ID 为1到5的行。

library(purrr) # for the map() function

# listing the ids we want to delete
ids = c(1,2,3,4,5)

# creating list of DELETE queries with each id
delete_queries = paste0("DELETE FROM tablename WHERE (id = '", ids, "');")

# executing each query
map(.x = delete_queries, .f = dbExecute, conn = con)

请注意我们使用dbExecute而不是dbSendQuery，因为它返回受影响的记录数，这样我们就可以确定操作是否发生了。

- aicesanchez

---

使用 stringr::str_c 或 glue::glue，您可以直接将“# create list”步骤放入 map 调用中。不过，这仍然是一个聪明的方法。 - ha-pu

Answer 3

这是一个使用胶水的版本，正如@ha-pu所建议的。

dbDeleteCustom = function(x, con, schema_name, table_name) {
      #x: 1-row dataframe that contains 1+ key columns, and 1+ update columns.
      if (nrow(x) != 1) stop("Input dataframe must be exactly 1 row")

      where_str  <- purrr::map_chr(colnames(x), ~glue::glue_sql("{`.x`} = {x[[.x]]}", .con = con))
      where_str  <- paste(where_str, collapse = " AND ")

      update_str <- glue::glue("DELETE FROM {schema_name}.{table_name} WHERE {where_str}")

      # Execute ------------------------------------------------------------------

      query_res <- DBI::dbSendQuery(con, update_str)
      rowsAffected <- dbGetRowsAffected(query_res)
      DBI::dbClearResult(query_res)

      return (rowsAffected)
    }

这段代码是基于这篇文章中的代码：如何使用R DBI传递data.frame进行UPDATE操作 附加思考： 这段代码需要根据id进行永久删除的适应。最佳实践是避免根据id永久删除记录。而是使用软删除的方式，即添加一个列来记录记录是否被删除/无效。上述代码是针对在桥接表中删除一行的情况编写的，可以进行软删除。

- Aaron C