Insert where not exist otherwise update

My users require to upload excel every day.

It contains records of users' particular. What I want to achieve is the INSERT WHERE the record does NOT EXIST otherwise UPDATE the records that exist in the database.

The matching criteria is the user identification number (text data type), not the user identifier.

How can I achieve it? 

Hi,


Please use aggregate to check whether user exists or not and use if widget to insert/update records accordingly.


Thanks

Line by line check and then insert can become very time consuming if you have a lot of rows to do so whatch out for performance issues.

You can also use an Advanced Query to do an "UPSERT" operation in MSSQL

https://myadventuresincoding.wordpress.com/2016/01/05/sql-server-how-to-write-an-upsert-using-merge/

Also similar pattern but inserting into a temporary table and then using the MERG command to update the final destination in bulk.


Hi Christopher,

As you mentioned that 'user identification number' is key criteria here , so you need to loop through each record that you uploaded from excel and then call an aggregate function to check if the Identification number in excel data is present in Entity or not and based on that you either insert or update you record(calling the Create or Update entity action) . Use if widget to check this.

[Also just for info , are this data going to be huge ,if so then you need to perform this operation in back-end asynchronously using a timer. ]

Hope it will helpful :)

 

@John Williams

I also prefer to use UPSERT statement to do the job but for some weird reasons, it did not work as expected. Even a simple INSERT statement does not work too.

I posted a new thread at this link, https://www.outsystems.com/forums/discussion/47819/advanced-sql-insert-merge-statements-not-working-as-expected/#Post175367