Hi Kim,

if we are talking of many, but really many records what we have implemented and we are very satisfied is done in two steps. 

On your case what matters is the loading the DB.

The option we made is, when filtering the records we write it to a csv file. 

After that we load the DB and it is very fast,  think about that. 

Regards 

Hi Kim,

Option 1 is not recommended as per the best practice, as it will bring avoid unnecessary trips to the database: https://success.outsystems.com/documentation/11/building_apps/data_management/data_operations/best_practices_for_fetching_data/.

Therefore, I recommend Option 2 which uses filter in corresponding aggregate.

Older posts with similar questions:

https://www.outsystems.com/forums/discussion/93557/external-sql-server-database/

@Kim Tubice : I have an entirely different proposal for you and this without loops

1. Create a Temporary Table : Create an entity called Categories_temp with the same schema as your external categories entity.
2. Fetch External Data ( SELECT {categories}.* from {categories} )
3. Bulk Insert into Temporary Table :  Perform a bulk insert into Categories_temp. You’ll need the physical table name for this. You can fetch it using:  (System) -> Entity where Entity.Name =  "categories_temp"
4. Synchronize Data with Local Entity :  Execute a MERGE SQL statement to update or insert records in your local categories_local table: MERGE INTO {categories_local} AS TargetUSING {categories_temp} AS SourceON Target.ExternalId = Source.idWHEN MATCHED THEN UPDATE SET Target.Description = Source.DescriptionWHEN NOT MATCHED BY TARGET THEN INSERT (ExternalId, Description)VALUES (Source.id, Source.Description); 
5. Clean Up Temporary Data :  Finally, clear out the temporary table: DELETE FROM {categories_temp};

Notes : 

• Concurrency Risk: This approach introduces a temporary entity (categories_temp), which can cause issues in parallel calls since the data isn't session-specific. To mitigate this, add a unique session identifier field (e.g., SessionId) to differentiate records per call.

• Batch Processing: It’s advisable to perform the sync in batches instead of processing the entire dataset in a single operation. This helps manage memory and database locks more efficiently.

Hi @Kim Tubice ,

Please find the below 2 solutions.

Solution 1 :

• Create a server action (e.g., GetCachedLocalCategories):

  • Fetch the local categories with caching enabled (Cache in Minutes = 20).

  • Output the list of ExternalIDs only (or a map/dictionary if you need fast lookup).

• In the main CreateUpdate logic:

  • Loop through GetCategories (external data).

  • For each item, use a ListFilter or lookup in a local map/dictionary to check existence.

  • If not found, add it to a bulk create list.

  • At the end of the loop, use a Bulk Insert/Create if possible.

Solution 2 :

Use a Set-Based Approach with a Batch Insert

Instead of looping, let the database do the heavy lifting by using a set-based operation. 

1. Fetch all External IDs from GetCategories.

2. Fetch all existing External IDs from the local table.

3. Use a server-side logic to calculate the difference (External IDs not in Local).

4. Batch insert only the new records.

You can do this with an aggregate or advanced SQL inside a Server Action:

SELECT E.*

FROM GetCategories E

LEFT JOIN LocalCategories L ON E.ExternalID = L.ExternalID

WHERE L.ExternalID IS NULL

Then insert the results into the local table in one go. 

Hope this works.

Thanks,

Senthil