3
 Followers
44
 Likes

Create bulk update, bulk insert and bulk delete widgets for sql ops

Backend
On our radar
Every once and a while, one needs to perform bulk operations on data, and to get the best performance reccurs to advanced queries.

The thing is that these widgets aren't "designed" for this ops (one has to always insert output parameter, when no parameter is required on these cases),

so my suggestion is : Create some new built-in widgets for this ops.

Best Regards,

Diogo C S Cordeiro
Created on 12 Nov 2010
Comments (23)
Hi Diogo,

It's a good plan, which I would like to add an extra viewpoint:

If you want to use the cloud (Amazon) for develop /demo.  It would be handy to "1-click" the database to and later on from the cloud.
This way you keep the entered information like ECT.

Best Regards,

Bert Leibbrand       <><
Merged this idea with 'Support for Bulk Insert and Update' (created on 2014-06-27 22:48:49 by Ravi Vakkalanka)
It would it much better to do 1000 inserts all at once rather than inserting 1000 records in a for loop. I am not sure if Outsystems 8 version has this support. Pls let me know if Bulk Insert / Update support is already there else its better to develop a widget which can support this functionality

Merged from 'Support for Bulk Insert and Update' (idea created on 2014-06-27 22:48:49 by Ravi Vakkalanka), on 2018-01-24 21:17:01 by J.
Ravi -

Bulk insert is supported in 8, it is the AsyncronousLogging Extension (not obvious from the name). I do NOT know if that also does bulk update or not, I have not tried.

J.Ja


Merged from 'Support for Bulk Insert and Update' (idea created on 2014-06-27 22:48:49 by Ravi Vakkalanka), on 2018-01-24 21:17:01 by J.
no it does not, just async insert.

bulk update is weird for several reasons ;)

but simple bulk updates you should do with advanced queries.



Merged from 'Support for Bulk Insert and Update' (idea created on 2014-06-27 22:48:49 by Ravi Vakkalanka), on 2018-01-24 21:17:01 by J.
For bulk INSERTS/UPDATES you should write an advanced query. I don't recomend using a loop to do this as you're going to increase CPU usage and consume much more resources in the Application Server.

Merged from 'Support for Bulk Insert and Update' (idea created on 2014-06-27 22:48:49 by Ravi Vakkalanka), on 2018-01-24 21:17:01 by J.

Hi guys,

Should I merge with this idea?

https://www.outsystems.com/ideas/554/create-bulk-update-bulk-insert-and-bulk-delete-widgets-for-sql-ops

It will increase the number of likes and merge the comments.

Cheers,



Merged from 'Support for Bulk Insert and Update' (idea created on 2014-06-27 22:48:49 by Ravi Vakkalanka), on 2018-01-24 21:17:01 by J.

just do it(tm)




Merged from 'Support for Bulk Insert and Update' (idea created on 2014-06-27 22:48:49 by Ravi Vakkalanka), on 2018-01-24 21:17:01 by J.
I use table variable parameters and bulk insert, update and merge statements to deal with bulk data.
It is much faster than processing a single line at a time.

A tvp is in effect a structure and Outsystems has that already, so all that is needed is the ability to bulk insert, update or merge.





Merged from 'Merge Statements and Bulk Insert, Update' (idea created on 2015-11-18 10:07:40 by Ronald Grobler), on 2018-01-24 21:18:16 by J.
Ronald -

Have you looked at the Asynchronous Logging extension? It's used for bulk INSERTs. As far as I can tell, it doesn't actually use SQL-style bulk insert, it queues things to be inserted, but it is still a decent answer for the need.

J.Ja


Merged from 'Merge Statements and Bulk Insert, Update' (idea created on 2015-11-18 10:07:40 by Ronald Grobler), on 2018-01-24 21:18:16 by J.
Hi Justin,

That is a type of solution for when it does not matter how long it takes to import/export the data.

But when there is a time constraint then the bulk insert, update and merge is a must.


Merged from 'Merge Statements and Bulk Insert, Update' (idea created on 2015-11-18 10:07:40 by Ronald Grobler), on 2018-01-24 21:18:16 by J.
It's pretty easy to bulkimport it yourself.
I believe there is an extension for it.
If not, I can dig up my solution for it.

In any case, it would rock if it's builtin




Merged from 'Merge Statements and Bulk Insert, Update' (idea created on 2015-11-18 10:07:40 by Ronald Grobler), on 2018-01-24 21:18:16 by J.

found another one, so merged that as well

Merged this idea with 'Delete All function for entity' (created on 07 Aug 2018 11:24:29 by Ramakrushna Rao Seera)

Hi All, 


As we are having functions like 

1.  Create<Entity>

2. CreateOrUpdate<Entity>

3. Update<Entity>

4. Get<Entity>

5. Get<Entity>ForUpdate

6. Delete<Entity>


If we can have Delete All function which takes list as input and delete all the records from Database which all  are in list base on ID. 


Why am asking is there times where we can have requirements to delete data from Particular entity .


Thanks & Regards,

Ramakrushna Rao Seera





This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James

Hi,

It could be useful, but dangerous at the same time, specially in Production environments.

You can however achieve that using a SQL query, and encapsulate it in a reusable Server Action. Your SQL query would look like this

Cheers



This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James

Yes we can write, Even we can write advance Query for everything but why do we have  aggregates since they are more optimized right.   


Could you please explain why it is dangerous to have? If a developer wanted to use then only he will use it otherwise he never uses it as like other functions



This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James

It is dangerous in the sense that if a developer leaves (by mistake) behind lets say a timer that runs that code go into production. Someone could trigger that code execution and all data would be lost.

In all cases, when used with caution you should be good.



This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James

Absolutely, something could be left by mistake, but at the same time, this could also be put as a warning in the warnings/errors panel.



This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James

This doesn't sound like it's asking for

"Delete from {MyEntitity}" but rather

"Delete from {MYEntitity} where id in [1,2,3,4]" where 1,2,3,4 is a list of IDs passed into the method.  

This functionality would be really good where you have a table of data, where each row has a checkbox and a "Delete" button to delete all the checked items. Just pass the comma list of ids or a List of Identity to the function.



This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James

Changed the category to Backend




This comment was:
- originally posted on idea 'Delete All function for entity' (created on 07 Aug 2018 by Ramakrushna Rao Seera)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:12:19 by Justin James
Merged this idea with 'Create new entity actions for massive create, update or delete' (created on 04 May 2017 09:54:18 by Rafael Valente)

The idea is to create entity actions (e.g., CreateEntity) that allow to create, update or delete many records at once.

In v10, we already have these action in mobile for Local Storage.



This comment was:
- originally posted on idea 'Create new entity actions for massive create, update or delete' (created on 04 May 2017 by Rafael Valente)
- merged to idea 'Create bulk update, bulk insert and bulk delete widgets for sql ops' on 22 Jan 2019 04:13:17 by Justin James
views
1764
Followers
3