Deleting all rows using delete from data store entity

Question

Hi, 
 I am trying to delete all the existing rows from the data entity using delete from data store entity smart service. Can anyone please suggest me what to include in identifiers if we want to delete everything without specifying all the identifiers? 
 Thanks 
 Yeswanth.

Anurag · Answer

Hi , 
 I found a way to delete multiple records in Appian. Below is steps for same: 
 1. Create a process model 
 2. Refer attached image for Process Model 
 3. Process Model logic: pass 1 input parameter to PP, and then configure delete operation, post increment unique PP value using script task, then check using OR that if PP value is max unique value in table, and accordingly again assign to delete 
 4. Run process model in debug mode 
 5. It will empty all records

Mauricio G. · Answer

You can recover all the ids with a query storing it in a variable of the CDT type that you want to delete. Then you can pass the list of results with the ids to the "delete from data store entity" smart service. 
 Or you can develop an stored procedure in your DDBB and initiate it with a procees model executing the "Execute Stored Procedure" plugin. https://community.appian.com/b/appmarket/posts/execute-stored-procedure 
 I use it before and works :D

aloks0189 · Answer

Hi Yeswanth, 
 As per my understanding, Stored Procedure is the best option to perform this operation on DB. Also, few points to consider here: 
 Dis-advantages of Query Database Smart Service 
 1. No doubt, Query Database smart service can also do this Job, but it's not recommended by Appian 
 2. When you are using Query Database smarts service, you need to write native SQL query/queries and hence any changes in DB Vendor or your requirement, will need you to modify the configuration of this smart service. However you can use environment constant to define the queries but still you need to update the Appian Object, some or the other way. 
 
 Advantage of Procedure 
 1. Once this query executes, the scheduling responsibility for completing the job will be taken care by DB Engines instead of Appian 
 2. Any Changes in requirement will need us to just modify the SQL script in DB side hence our Appian objects will remain unchanged. 
 3. We can execute the procedure on the Interface using a function as well as inside the process using the smart service, means we have an opportunity to perform this operation on Interface as well as Process Level. 
 4. Performance wise it's much better. 
 
 Hope the above comparison, will give you the idea about why Stored Procedure stands better while compared to Query DB Smart Service.

mayurm168 · Answer

You can use query database smart service and use query like below 
 Delete from tableName where nameOfPrimaryKeyColumn > 0 
 
 Hope this helps!

Deleting all rows using delete from data store entity

Top Replies