Menu
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?
885
To remove the rows in R, use the subsetting in R. There is no built-in function of removing a row from the data frame, but you can access a data frame without some rows specified by the negative index. This process is also called subsetting. This way, you can remove unwanted rows from the data frame.
To remove one or more rows in a table: First, you specify the table name where you want to remove data in the DELETE FROM clause. Second, you put a condition in the WHERE clause to specify which rows to remove. If you omit the WHERE clause, the statement will remove all rows in the table.
To delete rows in a SQL Server table, use the DELETE statement: delete from sessions where id = 10; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.
Introduction to SQL Server DELETE statement. To remove one or more rows from a table completely, you use the DELETE statement. The following illustrates its syntax: First, you specify the name of the table from which the rows are to be deleted in the FROM clause.
R scripts and packages use DBI to access various databases through their DBI backends. The interface defines a small set of classes and methods similar in spirit to Perl’s DBI, Java’s JDBC, Python’s DB-API, and Microsoft’s ODBC. It supports the following operations: Most users who want to access a database do not need to install DBI directly.
Here are two approaches to delete records from a table in SQL Server: (1) Delete records based on specified conditions: DELETE FROM
In this case, you need to specify the search_condition in the WHERE clause to limit the number of rows that are deleted. The rows that cause the search_condition evaluates to true will be deleted. The WHERE clause is optional. If you skip it, the DELETE statement will remove all rows from the table. Let’s create a new table for the demonstration.
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().
71
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
