Deleting all rows using delete from data store entity

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.

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer
    Hi Yashwanth,

    I don't think you can delete all the rows without specifying all the identifiers using delete from data store entity smart service. However you can write a stored proc to delete everything from the table.

    Thanks,
    Nitesh
  • 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

  • Hello Yeswanth,

    The suggestions above are good I just want to add some notes here.
    If you want to delete everything and the table is not huge maybe passing all the IDs is ok (maybe no more than 100-500 even 1000.

    What I would prefer is to use the execute store procedure as suggested by Mauricio, my suggestion here is tu use inside the stored procedure the “truncate table” instruction which deletes everything from a table. I strongly recomend this option since it is faster and it has some other advantages.

    Links that
    dev.mysql.com/.../truncate-table.html

    Hope this helps

    Jose
  • 0
    A Score Level 1
    in reply to josep
    Hi Yeswanth,

    As suggested by others, Stored procedure is the best approach when you're dealing with many rows. You need to use the Executed Stored Procedure Plugin, it is pretty straight forward.

    community.appian.com/.../execute-stored-procedure

    Thanks,
    Sindhu
  • 0
    Certified Lead Developer

    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 

  • 0
    Certified Lead Developer
    in reply to Anurag

    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 Slight smile

    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 Slight smile