Using Upsert operation to create\/update child record and relate to existing parent record with help of\u00a0External Id<\/strong><\/p>\n Lets assume that you have list of contacts and Account in external SQL Server database and you want to import those Account and\u00a0contact weekly. It is possible that you have already imported contacts previously\u00a0so don’t want to duplicate records\u00a0if already created in Salesforce. To solve this problem we can use external Id on Contact and use upsert operation on dataloader. Let’s assume external Id field on Contact is “SQL_Server__Id__c” (I am assuming that Account records in Salesforce also have external Id and records are already imported. Same below\u00a0steps can be used to import Account record as well).<\/p>\n How to use upsert operation in Apex<\/strong><\/p>\n We can upsert records in Apex also, as shown in below code snippet<\/p>\n Above code snippet will perform upsert operation on all Contact with field ‘SQL_Server_Id__c’. If any one record will fail then none of contact record will be upserted. You may want to perform partial operation where if any record fails then it should not rollback other records processing. In this scenario Database.upsert<\/strong> comes to rescue. Below code snippet shows how we can use Database.upsert and how to iterate through errors.<\/p>\n How to insert parent and child record in single statement in Apex using External Id<\/strong><\/p>\n As informed earlier in this post, we can use external Id of Parent record to insert parent and child in Same statement<\/p>\n <\/p>\n What if there are more than one records in Salesforce with Same external Id ?<\/strong><\/p>\n In this case Dataloader or Apex will throw an error saying “Duplicate external id specified”.<\/p>\n Making external Id as a unique\u00a0<\/strong><\/p>\n While trying to create External Id field, we have option to select whether field should be unique or not. If field already exists and we want to change it as unique external Id then it must be populated with unique values before making it unique.<\/p>\n\n
![\"Salesforce](\"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/05\/Dataloader-Settings.png?ssl=1\")
<\/a>\n
<\/a>\n
<\/a>\n
<\/a>\n
\r\nList lstContact = new List();\r\nContact con = new Contact (lastName = 'Zaa', SQL_Server_Id__c='3',firstName='Jitendra');\r\nlstContact.add(con);\r\n\/\/.. Other Contact records added in List\r\nupsert lstContact SQL_Server_Id__c;\r\n<\/pre>\n
\r\nList<Contact> lstContact = new List<Contact>();\r\nContact con = new Contact (lastName = 'Zaa', SQL_Server_Id__c='3',firstName='Jitendra');\r\nlstContact.add(con);\r\n\/\/.. Other Contact records added in List\r\nDatabase.UpsertResult[] results = Database.upsert( lstSGAccOppInsert, Contact.SQL_Server_Id__c.getDescribe().getSObjectField() ,false ) ;\r\n\r\nfor(Integer i=0;i<results.size();i++){\r\n if (!results.get(i).isSuccess()){\r\n Database.Error err = results.get(i).getErrors().get(0);\r\n System.debug('Error - '+err.getMessage() + '\\nStatus Code : '+err.getStatusCode()+'\\n Fields : '+err.getFields());\r\n }\r\n}\r\n<\/pre>\n\r\n\/\/Create instance of Child record\r\nContact con = new Contact (lastName = 'Zaa', SQL_Server_Id__c='3',firstName='Jitendra');\r\n\r\n\/\/Create instance of Parenr record and only specify External Id, No Other fields\r\nAccount accountReference = new Account( Account_External_Id__c='21'); \r\n\r\n\/\/relate Child record with Parent\r\ncon.Account = accountReference;\r\n\r\nAccount parentAccount = new Account( Name='Cognizant', MyExtID__c='21'); \r\n\r\n\/\/ Create the account and the Contact.\r\nDatabase.SaveResult[] results = Database.insert(new SObject[] { parentAccount, con });\r\n<\/pre>\n