I have two objects Object1 and Object2 in Salesforce. These two objects doesn't have Master-Detail relationship. But they are related through a Text column X. I want to create many to many junction object Object3, which will link Master-Detail Object1 and Object2. I had two approaches in mind.
- Create an Apex Job
- Loop through Object1
- Find all Object2 which matches with the field Object1.X = Object2.X
- Create a Junction Object Object3 with Object1,Object2 as Master-Detail relationship.
Limitations: I am working in Millions of records and there are many limitation in number of records retrieved, number of SOQL triggered and number of Batches that can be created per day.
- Pull all the records from Object1 to our System using Bulk API query
- Pull all the records from Object2 to our System using Bulk API query
- Do a join in Mysql with field X as Join condition
- Create Junction objects with join on Object1.X = Object2.X using Bulk API
Limitations: Bulk API cannot retrieve CSV output greater than 1 GB.
I tried using Limit Offset to retrieve records in batches but Bulk API doesn't support offset queries.
Please help me understand which one is better and if there is any simpler approach to do the same.