1

EDIT : besides the answer by Gordon, I found this question answered beautifully on this thread

While we can execute a group of insert/update statements as a batch, there is no such provision for select statements. I understand that it's impossible to do so because they return results that may have many columns.

However, SELECT COUNT(*) FROM table_name returns just one value and that is an integer. Is there a way to run a group of such queries as a batch and retrieve the results as an int array?

My particular requirement is to run these queries for a bunch of tables on the same database and then repeat this for more databases. Consider this example:

    String[] queries = {"SELECT COUNT(*) FROM table1",
                        "SELECT COUNT(*) FROM table2",
                        "SELECT COUNT(*) FROM table3" };
    Statement st = connection.createStatement();
    for(String q: queries){            
           st.addBatch(q);
    }       
    st.executeBatch();

In this case, the executeBatch() command will only return the number of rows affected, which will be zero (I suppose) in this case for each statement.

Is there a way to make a batch of such statements considering that they return only one value each? Any workaround?

Specific requirements follow..

SQL Procedures are out of question as I have to do this on many different databases and I don't have any access except to read them. I mean, I can store procedures there, but I've been told not to modify the database in any way and just to read from it. Additionally, the number of databases may increase later and I wouldn't be there to install these procedures on them, so I don't want that dependency in order for this solution to be truly flexible.

I am using Java with SQL Server 2008 for building a webpage that gets count values from a bunch of tables on various databases and displays them.

Community
  • 1
  • 1
Aamil Syed
  • 117
  • 6

1 Answers1

2

Why not just run one query? You an do:

select (SELECT COUNT(*) FROM table1) as table1cnt,
       (SELECT COUNT(*) FROM table2) as table2cnt,
       (SELECT COUNT(*) FROM table3) as table3cnt;

You can also return one value per row, but you'll want an additional column to identify the table:

select which, cnt
from ((SELECT 'table1' as which, COUNT(*) as cnt FROM table1) union all
      (SELECT 'table2' as which, COUNT(*) as cnt FROM table2) union all
      (SELECT 'table3' as which, COUNT(*) as cnt FROM table3)
     ) t
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786