Table of Contents


Upserting Records (INSERT OR UPDATE)

Summary

This section covers the upsert operation, which automatically inserts a new record or updates an existing record based on matching criteria. Upsert is particularly useful for data synchronization, imports, and integration scenarios.

Understanding Upsert

Upsert combines insert and update operations into a single call. The system determines whether to create or update based on:

  • Record ID: If the record has a valid ID that exists in the database, it updates
  • External ID Field: If an external ID field is specified and matches an existing record, it updates
  • No Match: If neither ID nor external ID matches, it creates a new record

When to Use Upsert:

  • Data imports from external systems
  • Synchronization operations
  • API integrations where you don't know if a record exists
  • Bulk operations mixing new and existing records

When NOT to Use Upsert:

  • When you know the operation is strictly create or update
  • When you need different logic for create vs update
  • When performance is critical and you can separate operations

Upsert by Record ID

The simplest form of upsert uses the record ID to determine insert or update.

Basic Upsert by ID:

// If ID exists, update; otherwise, create
var account = new Account
{
    Id = "001R000000abc123",  // Existing ID
    Name = "Partner Company",
    Type = "Partner",
    Industry = "Technology"
};

Database.Upsert(account);

Upsert New Record (No ID):

// No ID provided - will insert
var account = new Account
{
    Name = "New Partner",
    Type = "Partner"
};

Database.Upsert(account);

Upsert Existing Record (With ID):

// Retrieve existing record
var account = Database.Retrieve<Account>(accountId);

// Modify fields
account.Phone = "(555) 123-4567";
account.Website = "https://example.com";

// Upsert will update since ID exists
Database.Upsert(account);

Mixed Upsert (Some New, Some Existing):

var accounts = new List<Account>
{
    new Account { Id = "001R000000abc123", Name = "Update This" },  // Exists - will update
    new Account { Name = "Create This", Type = "Partner" },         // New - will insert
    new Account { Id = "001R000000xyz789", Name = "Update This Too" } // Exists - will update
};

Database.Upsert(accounts);

Upsert by External ID

External ID fields allow matching records based on unique identifiers from external systems.

Basic Upsert by External ID:

// Match on external ID field
var account = new Account
{
    ExternalId__c = "EXT-12345",  // External system identifier
    Name = "Partner Company",
    Type = "Partner",
    Industry = "Technology"
};

var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c"
});

Common External ID Patterns:

// Email as external ID for contacts
var contact = new Contact
{
    Email = "john.smith@example.com",  // Unique email
    FirstName = "John",
    LastName = "Smith",
    AccountId = accountId
};

Database.Upsert(contact, new DatabaseOptions 
{ 
    ExternalIdField = "Email"
});

// SKU as external ID for products
var product = new Product
{
    ProductCode = "SKU-12345",
    Name = "Product Name",
    Price__c = 99.99m
};

Database.Upsert(product, new DatabaseOptions 
{ 
    ExternalIdField = "ProductCode"
});

// Order number as external ID
var order = new Order
{
    OrderNumber__c = "ORD-2024-001",
    OrderDate__c = DateTime.UtcNow,
    TotalAmount__c = 1500.00m
};

Database.Upsert(order, new DatabaseOptions 
{ 
    ExternalIdField = "OrderNumber__c"
});

Bulk Upsert by External ID:

var contacts = new List<Contact>
{
    new Contact { Email = "john@example.com", FirstName = "John", LastName = "Smith" },
    new Contact { Email = "jane@example.com", FirstName = "Jane", LastName = "Doe" },
    new Contact { Email = "bob@example.com", FirstName = "Bob", LastName = "Johnson" }
};

Database.Upsert(contacts, new DatabaseOptions 
{ 
    ExternalIdField = "Email"
});

int insertCount = results.Count(r => !r.HasError && r.IsInsert);
int updateCount = results.Count(r => !r.HasError && !r.IsInsert);

SystemInfo.Debug($"Inserted: {insertCount}, Updated: {updateCount}");

Bulk Upsert Operations

Bulk upsert is ideal for data synchronization and import operations.

Import Pattern with Upsert:

public void ImportAccountsFromExternalSystem(List<ExternalAccount> externalAccounts)
{
    var accountsToUpsert = new List<Account>();
    
    // Prepare accounts (no database calls in loop)
    foreach (var external in externalAccounts)
    {
        accountsToUpsert.Add(new Account
        {
            ExternalId__c = external.SystemId,
            Name = external.CompanyName,
            Type = external.Type,
            Industry = external.Industry,
            Phone = external.Phone,
            Website = external.Website
        });
    }
    
    // Single bulk upsert
    var results = Database.Upsert(accountsToUpsert, new DatabaseOptions 
    { 
        ExternalIdField = "ExternalId__c",
        IsApiMode = true 
    });
    
    // Track results
    int inserted = 0;
    int updated = 0;
    int errors = 0;
    
    foreach (var result in results)
    {
        if (result.HasError)
        {
            errors++;
        }
        else if (result.IsInsert)
        {
            inserted++;
        }
        else
        {
            updated++;
        }
    }
    
    SystemInfo.Debug($"Import complete: {inserted} created, {updated} updated, {errors} errors");
}

Synchronization Pattern:

public void SyncContactsFromCRM(List<CrmContact> crmContacts)
{
    var contactsToSync = new List<Contact>();
    
    foreach (var crm in crmContacts)
    {
        contactsToSync.Add(new Contact
        {
            Email = crm.Email,  // Use email as matching field
            FirstName = crm.FirstName,
            LastName = crm.LastName,
            Phone = crm.Phone,
            Title = crm.Title,
            LastSyncDate__c = DateTime.UtcNow
        });
    }
    
    Database.Upsert(contactsToSync, new DatabaseOptions 
    { 
        ExternalIdField = "Email",
    });    
}

Large Bulk Upsert (Batching):

public void BulkUpsertAccounts(List<Account> accounts)
{
    int batchSize = 200;
    int totalBatches = (int)Math.Ceiling((double)accounts.Count / batchSize);
    
    int totalInserted = 0;
    int totalUpdated = 0;
    int totalErrors = 0;
    
    for (int i = 0; i < totalBatches; i++)
    {
        var batch = accounts.Skip(i * batchSize).Take(batchSize).ToList();
        
        var results = Database.Upsert(batch, new DatabaseOptions 
        { 
            ExternalIdField = "ExternalId__c",
            IsApiMode = true 
        });
        
        int inserted = results.Count(r => !r.HasError && r.IsInsert);
        int updated = results.Count(r => !r.HasError && !r.IsInsert);
        int errors = results.Count(r => r.HasError);
        
        totalInserted += inserted;
        totalUpdated += updated;
        totalErrors += errors;
        
        SystemInfo.Debug($"Batch {i + 1}/{totalBatches}: {inserted} inserted, {updated} updated, {errors} errors");
    }
    
    SystemInfo.Debug($"Total: {totalInserted} inserted, {totalUpdated} updated, {totalErrors} errors");
}

Error Handling

Proper error handling for upsert operations.

Exception Mode (default):

try
{
    var account = new Account
    {
        ExternalId__c = "EXT-001",
        Name = ""  // Invalid - required field
    };
    
    var result = Database.Upsert(account, new DatabaseOptions 
    { 
        ExternalIdField = "ExternalId__c"
    });
}
catch (Exception ex)
{
    SystemInfo.Debug($"Upsert failed: {ex.Message}");
}

Result Mode:

var account = new Account
{
    ExternalId__c = "EXT-001",
    Name = ""  // Invalid - required field
};

var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
    IsApiMode = true 
});

if (result.HasError)
{
    foreach (var error in result.Errors)
    {
        SystemInfo.Debug($"Upsert failed - {error.PropertyName}: {error.Message}");
    }
}

Common Upsert Errors:

// External ID field not found
var account = new Account
{
    ExternalId__c = "EXT-001",
    Name = "Test"
};

var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "NonExistentField__c",  // Error: Field not found
});

// Multiple matches found
// Error occurs if external ID field is not unique and matches multiple records
var account = new Account
{
    Phone = "(555) 123-4567",  // Multiple accounts have this phone
    Name = "Test"
};

var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "Phone",  // Error: Multiple records found
});

// Required field missing on insert
var account = new Account
{
    ExternalId__c = "NEW-001",  // New record
    Type = "Partner"  // Missing required Name field
};

var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
});
// Error: Name is required

Bulk Upsert Error Collection:

public class UpsertResult
{
    public int TotalRecords { get; set; }
    public int InsertedCount { get; set; }
    public int UpdatedCount { get; set; }
    public int ErrorCount { get; set; }
    public List<string> Errors { get; set; }
}

public UpsertResult BulkUpsertWithErrorTracking(List<Account> accounts)
{
    var results = Database.Upsert(accounts, new DatabaseOptions 
    { 
        ExternalIdField = "ExternalId__c",
        IsApiMode = true 
    });
    
    var upsertResult = new UpsertResult
    {
        TotalRecords = accounts.Count,
        InsertedCount = 0,
        UpdatedCount = 0,
        ErrorCount = 0,
        Errors = new List<string>()
    };
    
    for (int i = 0; i < results.Count(); i++)
    {
        var result = results.ElementAt(i);
        var account = accounts[i];
        
        if (result.HasError)
        {
            upsertResult.ErrorCount++;
            upsertResult.Errors.Add($"Row {i + 1} - {account.ExternalId__c}: {result.Errors[0].Message}");
        }
        else if (result.IsInsert)
        {
            upsertResult.InsertedCount++;
        }
        else
        {
            upsertResult.UpdatedCount++;
        }
    }
    
    return upsertResult;
}

Upsert Options (DatabaseOptions)

DatabaseOptions controls upsert operation behavior.

External ID Field:

// Specify which field to use for matching
Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c"
});

// Different external ID fields for different entities
Database.Upsert(contact, new DatabaseOptions 
{ 
    ExternalIdField = "Email"
});

Database.Upsert(product, new DatabaseOptions 
{ 
    ExternalIdField = "ProductCode" 
});

IsApiMode:

// Return errors in result (recommended)
var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
    IsApiMode = true 
});

if (result.HasError)
{
    // Handle errors in result
}

SystemMode:

// Upsert with system privileges
var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
    SystemMode = true,
    IsApiMode = true 
});
⚠ Warning: Always document why SystemMode = true is required.

AllOrNone (Transactional Mode):

// All records must succeed or all fail
var results = Database.Upsert(accounts, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
    AllOrNone = true,
    IsApiMode = true 
});

// If any record fails, entire operation is rolled back
if (results.Any(r => r.HasError))
{
    SystemInfo.Debug("Transaction failed - no records were inserted or updated");
}

Combined Options:

var results = Database.Upsert(accounts, new DatabaseOptions
{
    ExternalIdField = "ExternalId__c",  // Match on external ID
    IsApiMode = true,                   // Return errors in results
    AllOrNone = true,                   // All succeed or all fail
    SystemMode = false                  // Respect user permissions
});

Upsert Operation Examples

Data Integration Pattern:

public void IntegrateExternalOrders(List<ExternalOrder> externalOrders)
{
    var ordersToUpsert = new List<Order>();
    
    foreach (var external in externalOrders)
    {
        ordersToUpsert.Add(new Order
        {
            OrderNumber__c = external.OrderId,
            OrderDate__c = external.OrderDate,
            TotalAmount__c = external.Total,
            Status__c = external.Status,
            CustomerEmail__c = external.Email,
            LastSyncDate__c = DateTime.UtcNow
        });
    }
    
    var results = Database.Upsert(ordersToUpsert, new DatabaseOptions 
    { 
        ExternalIdField = "OrderNumber__c",
        IsApiMode = true 
    });
    
    int newOrders = results.Count(r => !r.HasError && r.IsInsert);
    int updatedOrders = results.Count(r => !r.HasError && !r.IsInsert);
    
    SystemInfo.Debug($"Integration complete: {newOrders} new orders, {updatedOrders} updated orders");
}

CSV Import with Upsert:

public void ImportContactsFromCSV(List<CsvRow> csvData)
{
    var contactsToUpsert = new List<Contact>();
    
    foreach (var row in csvData)
    {
        contactsToUpsert.Add(new Contact
        {
            Email = row.Email,
            FirstName = row.FirstName,
            LastName = row.LastName,
            Phone = row.Phone,
            Title = row.Title
        });
    }
    
    var results = Database.Upsert(contactsToUpsert, new DatabaseOptions 
    { 
        ExternalIdField = "Email",
        IsApiMode = true 
    });
    
    // Generate import report
    var report = new StringBuilder();
    report.AppendLine("Contact Import Report");
    report.AppendLine("=====================");
    
    for (int i = 0; i < results.Count(); i++)
    {
        var result = results.ElementAt(i);
        var contact = contactsToUpsert[i];
        
        if (result.HasError)
        {
            report.AppendLine($"Row {i + 1} - ERROR: {contact.Email} - {result.Errors[0].Message}");
        }
        else if (result.IsInsert)
        {
            report.AppendLine($"Row {i + 1} - CREATED: {contact.Email}");
        }
        else
        {
            report.AppendLine($"Row {i + 1} - UPDATED: {contact.Email}");
        }
    }
    
    SystemInfo.Debug(report.ToString());
}

Scheduled Sync Pattern:

public void SyncAccountsDaily()
{
    // Get accounts from external API
    var externalAccounts = GetExternalAccountData();
    
    var accountsToSync = new List<Account>();
    
    foreach (var ext in externalAccounts)
    {
        accountsToSync.Add(new Account
        {
            ExternalId__c = ext.Id,
            Name = ext.Name,
            Industry = ext.Industry,
            Phone = ext.Phone,
            Website = ext.Website,
            AnnualRevenue = ext.Revenue,
            LastSyncDate__c = DateTime.UtcNow
        });
    }
    
    // Batch upsert
    int batchSize = 200;
    int totalBatches = (int)Math.Ceiling((double)accountsToSync.Count / batchSize);
    
    for (int i = 0; i < totalBatches; i++)
    {
        var batch = accountsToSync.Skip(i * batchSize).Take(batchSize).ToList();
        
        var results = Database.Upsert(batch, new DatabaseOptions 
        { 
            ExternalIdField = "ExternalId__c",
            IsApiMode = true 
        });
        
        SystemInfo.Debug($"Sync batch {i + 1}/{totalBatches} complete");
    }
}

Best Practices for Upsert Operations

✅ Always specify ExternalIdField when not using record ID

var result = Database.Upsert(account, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
});

✅ Ensure external ID fields are unique and indexed

// External ID field should be:
// - Unique across all records
// - Indexed for performance
// - Required (not null)

✅ Use bulk operations for multiple records

// ✅ GOOD
var results = Database.Upsert(accounts, new DatabaseOptions 
{ 
    ExternalIdField = "ExternalId__c",
    IsApiMode = true 
});

// ❌ BAD
foreach (var account in accounts)
{
    Database.Upsert(account, new DatabaseOptions 
    { 
        ExternalIdField = "ExternalId__c"
    });
}

✅ Check IsInsert to determine operation type

if (!result.HasError)
{
    if (result.IsInsert)
    {
        SystemInfo.Debug("Created new record");
    }
    else
    {
        SystemInfo.Debug("Updated existing record");
    }
}

✅ Use upsert for integration and sync scenarios

// Perfect for:
// - External system synchronization
// - Data imports with unknown state
// - API integrations
// - Scheduled data updates

❌ Don't use upsert when operation type is known

// ❌ BAD - You know it's an update
var account = Database.Retrieve<Account>(accountId);
account.Name = "Updated";
Database.Upsert(account);  // Use Edit() instead

// ✅ GOOD
Database.Edit(account);
Last updated on 1/8/2026

Attachments


Privacy & Security StatementTerms & Conditions
Copyright © 2026 by Magentrix Corporation - All rights reserved.
Powered by Magentrix