Insert data into custom table

[ujwalabedar]

Hi all,

I want to insert data into custom table by fetching the data from software installation table  after applying some filter and software install table has millions of records in it. Can anyone suggest best way to do it?

[Maniteja_2003]

Hi @ujwalabedar ,

I hope this answer helps you.

I have one question: Do you have a strong use case that requires creating a custom table? If you are trying to solve a specific problem by creating a custom table, please let me know the use case or the problem you are trying to address. We may be able to achieve the same requirement without creating a custom table.

Thanks,

Maniteja.

[Tejas Adhalrao]

hi @ujwalabedar  ,

Since the Software Installation table has millions of records, I would avoid querying everything and then looping through it.

Use a Scheduled Script/Background Script with a well-indexed filter, fetch only the required fields, and process the records in batches. Also check whether the fields used in your filter are indexed; indexing will make a big difference at this volume.

If this is a recurring requirement, consider using a scheduled job that processes only new/updated records instead of rebuilding the entire custom table every time. Also handle duplicates using a unique key/coalesce check before inserting.

If this helps, please mark it as Helpful and accept the solution.
Regards,
Tejas

[Aditya_hublikar]

Hello @ujwalabedar ,

For millions of records, I would recommend using a Scheduled Script Execution with a batch-and-checkpoint approach. This gives you better control over the process, as you can decide the batch size, save the progress after each run, and resume from the last processed record if anything fails.. Before choosing approach, it is also worth checking whether you actually need to duplicate the data. A Database View, reference field, or table extension might satisfy the requirement without creating millions of duplicate records, which would save storage and avoid synchronization issues.

If this helps you then mark it as helpful and accept as solution.

Regards,

Aditya

[Tanushree Maiti]

Hi @ujwalabedar 

What kind of data do you want to fetch from the Software Installation table and insert into the custom table?

In my experience, we faced an issue with the Software Subscriptions table, where the user-related columns were not getting populated for around 10K out of 176K records.

I initially tried manipulating the data using a Scheduled Script Execution, but the results were not as expected.  At backend, job was not processing all that huge count records (all time)

Therefore, during the insert and update operations, I implemented a Before Business Rule to manipulate and populate the required data. This approach worked as expected.

Though you are facing the problem with software installation table, still you can try the same approch.