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

By Diogo Cordeiro on 12 Nov 2010
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
Bert Leibbrand12 Nov 2010
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       <><
Ravi Vakkalanka27 Jun 2014
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.
Justin James28 Jun 2014
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.
J.30 Jun 2014
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.
Gonçalo Almeida29 Jul 2014
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.
Vasco Pessanha24 Jan (4 weeks ago)

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.
J.24 Jan (4 weeks ago)

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.
Ronald Grobler18 Nov 2015
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.
Justin James19 Nov 2015
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.
Ronald Grobler20 Nov 2015
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.
J.21 Nov 2015
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.
J.24 Jan (4 weeks ago)

found another one, so merged that as well