Bulk Insertion of Records in Salesforce by Generating Apex Script

Author

mirketa

May 22, 2023

Every time someone wants to insert bulk records or multiple records into Salesforce, they will be choosing data insertion tools like Data Loader, Workbench, dataloader.io, and so on. Ok, wait! Can we use these tools to directly insert the child records (related list records) using a CSV file? Definitely not!

You are also aware that you can insert records using Apex scripting, but here the question is: Can we insert bulk records using Apex script? Now let me share the guide with you to do this using the Excel macro concept and generating Apex Script for the bulk records. This way, it really helps with how to insert multiple records in Salesforce using Apex.

Let’s dive in and insert parent and child records using Apex. For that, let’s make sure Microsoft Excel has the Developer tab in its ribbon.

How to get the Developer tab in Excel?

For this go to File tab in Excel > go to Options at the bottom > now select Customize the Ribbon > under Main Tabs, select the Developer check box.

Scenario: Insert Template Procedures (parent records) with their Actual Price Records (related list records) along with Modifiers (self-lookup field records).

First, prepare the database to be inserted into Excel. Make sure you also include mandatory fields in your data without missing.

Insertion of Self-lookup field records:

1. For this, first you must insert modifiers (self-lookup field records). You can also insert these records using Apex Script. For this, create a sample Apex script for inserting a single record.

2. Now comes the critical part of generating this Apex script for these multiple records in Excel.

How is Visual Basic in Excel used when inserting records in Salesforce?

Go to the Developer tab in Excel. Now click on Visual Basic and write the script, including the Excel columns that have the matching fields. For the modifiers insertion, this is the script.

3. Next, go to Macros in the Developer tab, select the Excel document, and click on Run.

You can now see the Apex script for the records. This script can be used (copied and pasted) to insert records from the Salesforce Anonymous Window.

We have your self-lookup field records inserted in the database.

Insertion of Parent records:

Now let’s insert template procedure records (parent records) along with related list records and self-lookup records in one go. Repeat the process the same as the previous insertion. Prepare the Excel sheet with all the required fields.

Let’s have a sample Apex script for the insertion of a single parent record along with the related list record and self-lookup field record.

Go to the Developer tab in Excel. Now click on Visual Basic and write the script, including the Excel columns that have the matching fields. For the Template Procedure Records (Parent Records) the Visual Basic script is shown as below:

Now the records to be inserted are present in the database along with their related list records. Using this method, you can insert bulk records with different labels and values.

Pros:

Efficiency: Bulk insertion allows for the efficient creation of multiple records in a single operation, reducing the overhead associated with individual record creation. This can significantly improve the performance and speed of data insertion, especially when dealing with many records or complex data relationships.
Consistency: When inserting related list records in bulk, the integrity and consistency of the data can be maintained. All related records are inserted or updated together, ensuring that the relationships between records are accurately established, and there are no inconsistencies or orphaned records.
Simplified Code and Error Handling: Bulk insertion can simplify the code required for data insertion, as it allows for a single operation to be performed instead of multiple individual operations. This can result in cleaner and more maintainable code in Visual Basic, as well as simplified error handling and recovery strategies.
Data Integrity: Bulk insertion of records along with related list records can help maintain data integrity by ensuring that all records are inserted or updated as a single transaction. This means that either all records are inserted, or none are, reducing the risk of incomplete or inconsistent data being stored in the database.
By Specifying the row number in Visual Basic Code, you can insert number of records of your choice.

Cons:

While bulk data insertion in Salesforce can be a convenient way to insert large volumes of data, including related list records, it also comes with potential disadvantages such as

Data relationship management challenges
Error handling and data reconciliation complexities and
The need for thorough testing and validation.

Pranshu Goyal, Director of Products at Mirekta, states: “We envision DSM to be used by every small to a medium-sized organization dealing with bad data and want to get rid of duplicates easily with no cost. We have faced issues dealing with duplicates in our organization. That inspired us to make a solution that is not only simple to use but can be used widely to make the organization’s data clean to make them more efficient and productive. We want DSM to be a solution for every organization looking for duplicate management capability better than the Salesforce out-of-the-box solution with no additional cost.”