0

(Warning: SQL Server expert, Salesforce N00b)

Hi All

What's the SOQL equivalent of 'SELECT COUNT(*) FROM SomeTable UNION..'?

I'm running an SSIS package that pumps data from SalesForce to SQL Server, and it would be great if I could also pump the row counts of all tables to use as an audit check that all rows were pumped successfully. Something like this:

SELECT 'Account' as label, COUNT(*) as row_count
FROM Account
UNION ALL
SELECT 'Assessment', COUNT(*)
FROM Assessment__c
UNION ALL
{all of my tables}

Thanks in advance. Jim

Improve this question

1 Answer 1

Reset to default
2

There's no such thing as UNION ALL, as you have probably found. I think the best thing to do is do a global describe, then loop through each object (table) and issue a COUNT() type SOQL query.

UPDATE: example code. Note that this fails for tables with >50K rows

// Returns a map of the object name (all lower case) to the type object
// You can identify custom objects by the trailing '__c' string
Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();

// declare the tables you care about; otherwise, the loop below will fail due to
// the 100 SOQL query limit
Set<String> tablesToCount = new Set<String>{'table1__c', 'table2__c', 'table3__c'};

// Store the results.  If you just need a grand total of rows, you can
// just increment an Integer variable, or loop through and count the values
// in the map at the end.
Map<String, Integer> map_tableName2RecordCount = new Map<String, Integer>();

for (String typeName : gd.keySet()) {

    // skip all but the tables you care about
    if (!tablesToCount.contains(typeName)) continue;
    System.debug('Now processing table ' + typeName);

    // issue query.  note that for dynamic SOQL (queries defined as Strings), 
    // you have to use the purpose-built Database.countQuery() method
    try {
        Integer count = Database.countQuery('SELECT COUNT() FROM ' + typeName);
        map_tableName2RecordCount.put(typeName, count);   
    } catch (Exception e) {

        // some objects have special query restrictions and will fail unless
        // those restrictions are met (typically additional WHERE conditions)
        System.debug('Oops. That query failed.  Here\'s why: ' + e.getMessage());
    }

}

// display the results
System.debug(map_tableName2RecordCount);
Improve this answer
7
  • Think its worth mentioning that counting queries the rows so by default only up to 50,000 can be counted.
    – Keith C
    Dec 18, 2014 at 18:09
  • @KeithC, is that true? I haven't tried this myself, but according to salesforce.stackexchange.com/a/13971/728 if you don't have a WHERE clause, it will count millions of rows.
    – ArtieBrosius
    Dec 18, 2014 at 18:50
  • You are right my comment isn't true based on a quick test of a table with 190,000 rows where the count works with or without a where clause.
    – Keith C
    Dec 18, 2014 at 19:05
  • 1
    The 50,000 row rule primarily applies to synchronous locking functions, such as triggers, Visualforce pages, rest and web services, etc. There's other limits that apply to users affected by sharing. Administrators, however, should have no problem getting a count of the number of rows for any table.
    – sfdcfox
    Dec 18, 2014 at 19:24
  • Artie - Can you further illustrate Global Describe and looping for me? Say three tables, Table1 - Table 2 - Table 3. I just started in SOQL a couple of months ago, so I haven't pulled either off before. Thanks in advance.
    – Jim Horn
    Dec 19, 2014 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .