Delete rows from SQL Server table using R (DBI package)

Question

I have a table in SQL server to which I am trying to add data. Before adding the data I want to delete all the existing records, but I do not want to delete the table and recreate it since it has index created in SQL server which I want to preserve.

What choices do I have to accomplish this using r?

Unrest

Answer 1

There are multiple ways to delete all records in a table.

You can TRUNCATE or DELETE

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

EDIT: use dbExecute() instead of dbSendQuery().

As commented in the documentation of dbSendQuery()

This method is for SELECT queries only. Some backends may support data manipulation queries through this method for compatibility reasons. However, callers are strongly encouraged to use dbSendStatement() for data manipulation statements.

However, send methods do not automatically clear the returned result object. Therefore get and execute methods are more suitable for interactive use. From the dbSendStatement() doc:

To query the number of affected rows, call dbGetRowsAffected() on the returned result object. You must also call dbClearResult() after that. For interactive use, you should almost always prefer dbExecute().

Naranjo

Answer 2

In case you need to delete ONLY certain records

To answer another use case when you need to delete only certain records from a database your could create a list of queries and then use map to execute them. For example, the next code would delete rows with id 1 through 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)

Note that we use dbExecuteinstead of dbSendQuery because it returns the number of records affected so we can be certain that the operations happened.

Subcutaneous

Using stringr::str_c or glue::glue you could put the "# create list" step directly into the map call. Still, smart approach. – Beckford

Answer 3

Here is a version using glue as @ha-pu suggested.

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)
    }

This code is based on code from this post: How to pass data.frame for UPDATE with R DBI

Side thoughts: This code would need to be adapted for permanently deleting based on id. Best practice is to avoid permanently deleting a record (based on id). Instead use a soft delete process, a column for recording if record is deleted/invalid. The code above was written for deletion of a row in a bridging table, which could be soft deleted.

Extragalactic