Deleting all rows using delete from data store entity

Hi,

Assuming that  you want to delete some test/dummy data from that table in local/Dev DB, you can use "Query Database" smart service.

Write a SQL query and delete all the records from table. But, before executing test it thoroughly.

Edited: For any business scenario you can use stored procedure for bulk delete as mentioned by fellow practitioners.

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

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.

You can use query database smart service and use query like below

Delete from tableName where nameOfPrimaryKeyColumn > 0

Hope this helps!

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 

You are aware that looping in a process model is NOT a best practice and will get you into trouble when you try to empty a table with a million rows?

Hi Stefan,

Thanks for pointing out that point

The option given by me is only it can be used when handling few (say less than 100 records) records. I had given the solution considering while developing new Application in Appian we usually generate test data which we need to delete to test again functionality. And many times developer dont have database access so they can consider this option.

Now for point where we need to delete million records:

1. First of all if it is required to delete records in such huge count, user should prefer taking backup of existing table and perform truncate operation (as delete will make your database slow)

2. Even using stored procedures which we call from Application i wont suggest, as it will impact Application Performance

3. If records count is in million, i would suggest to perform any operation directly using Oracle SQL Developer, MYSQL Server etc and not by app code.

Above points i have mentioned considering my previous experience. Also with appian, i currently have less than a month of experience...so there could be also other options available which i am yet to find.

Always up for any new ideas/suggestions