Hi all,

       I got stuck with the limit exception error in Salesforce. I'll explain the situation first, we are trying to copy about quarter million products from a custom object to Standard Product Object in salesforce and in the mean time we are also creating entries in PricebookEntry object. We want to do both these tasks using a Single Batch Apex.

I've tried the following the methods for solving this problem

1) Wrote a Batch Apex which has following things written in the execute method

    a) Loop which Upserts Products to Standard Product Object from Custom Object

    b) Loop through the Standard Product Object and create pricebook entries in a seperate loop.

Result : Got exception "Too many queries"

2) Wrote a Batch Apex with a single loop, instead of adding them to a list and then adding it by batch, I've tried to upsert products to Standard Product Object one by one also creating pricebook entries at the same time

Result : Got exception "Too many DML Statements"

What would be the best methodology to use in this situation?

I'm also adding my code snippets with this,

Batch Apex with two loops

global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      productstoupload = new List<Product2>();      
      for(sObject s : scope){
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;           
      productstoupload.add(prod);                      
    }       
    upsert productstoupload SF_ID__c;       
   
  //Upserting entries from Product2 to PricebookEntry
 
    //pricebookupdate = new List<PricebookEntry>();
    pricebookinsert = new List<PricebookEntry>(); 
   
 
   
    List<sObject> batch = [Select Id, Price__c, Availability_Status__c FROM Product2];
       
     for(sObject s : batch){
    
         Product2 prod = (Product2)s;               
         PricebookEntry pbe = new PricebookEntry();     
         pbe.Pricebook2Id=pricebook.ID;       
         pbe.Product2Id=prod.Id;
         pbe.UnitPrice=prod.Price__c;
        
         //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
         if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
         pbe.IsActive= false;
         }                
         else{        
         pbe.IsActive= true;        
         }
        
         pbe.UseStandardPrice=false;
        
        //Checking whether there is a Pricebook Entry exist for a particular product
     
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
                    
       //If there is no existing entry then we will insert a new Pricebook Entry
                    
          if(exist.isEmpty()){
          pricebookinsert.add(pbe);
          }
         
       //Else we will update the existing entry
         
          else{      
          //pricebookupdate.add(pbe);
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbupd.IsActive=false;
          }
          else{
          pbupd.IsActive=true;
          }
          pbupd.UnitPrice=prod.Price__c;
          update pbupd;         
         
         
          }
         
    } 
   
    insert pricebookinsert;
   
   // update pricebookupdate;
   
  }

Batch Apex with Single Loop:

 global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      for(sObject s : scope){
     
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();     
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;                 
      upsert prod SF_ID__c;
    
          PricebookEntry pbe = new PricebookEntry();     
          pbe.Pricebook2Id=pricebook.ID;       
          pbe.Product2Id=prod.Id;
          pbe.UnitPrice=prod.Price__c;
     
      //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbe.IsActive= false;
          }                
          else{        
          pbe.IsActive= true;        
          }               
         pbe.UseStandardPrice=false;
        
        
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
        
         if(exist.isEmpty()){
         insert pbe;
         //pricebookinsert.add(pbe);
         }
        
         else{                    
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
         
              if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
              pbupd.IsActive=false;
              }
              else{
              pbupd.IsActive=true;
              }
         
              pbupd.UnitPrice=prod.Price__c;
              update pbupd;        
         }
    }       
   
    //insert pricebookinsert;                  
 
  }

It would be a great help, if someone could provide any help on this issue.

Many Thanks,

Joe