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

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

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?

Solution

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().