Hello OutSystems community,
I'm unfortunately having a problem updating large data sets. The following: I'm currently importing a CSV file using Bulk Insert & CSV Util. After importing, I want to modify data (e.g. a department number "ABC" -> "XYZ" for every department with "ABC"). The data sets are quite large (at least 400,000+ rows). This makes simple loops inefficient.
I've already created new attributes within the aggregate I'm using, and it works through that. However, with 400,000 entries, this already takes 23 seconds and has to be reloaded with every fetch. Therefore, I would like a function directly in the insert that I can use to handle the whole process once during the import. If it takes a little longer there, it's okay, since the import occurs very rarely.
I would really appreciate any help :)
Hi Phillipe,
For a bulk update we should avoid unnecessary loops, and also it is not a good option from performance stand point, rather we should use advance sql with one line statement. Update {TableName} set {TableName}.[Attribute] = 'XYZ' where {TableName}.[Attribute ] = 'ABC'.
THanks,
Sachin
I see, at the moment i try to use SQL but there my Statements return no query...UPDATE {DB}SET {DB}.[Department] = CASE {DB}.[Department]WHEN 'ABC' THEN 'XYZ'WHEN 'DEF' THEN 'UVW'...ELSE {DB}.[Department] ENDWHERE {DB}.[Department] IN ( 'ABC', 'DEF', ...)
Hello.
Sachin's answer is the fast and easier to read.
If you really need to do it in Insert instead of update, just go with CASE while doing the select:SELECT
{Source}.[name],
CASE {Source}.[department], WHEN 'ABC' THEN 'XYZ' ELSE {Source}.[department],
{Source}.[Address],
...
Same as Sachin,I see, at the moment i try to use SQL but there my Statements return no query...UPDATE {DB}SET {DB}.[Department] = CASE {DB}.[Department]WHEN 'ABC' THEN 'XYZ'WHEN 'DEF' THEN 'UVW'...ELSE {DB}.[Department] ENDWHERE {DB}.[Department] IN ( 'ABC', 'DEF', ...) thanks so far also @Sachin Mahawar