Getting rowcount from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting rowcount from multiple tables

Author

Topic

DazedAndConfused
Starting Member

5 Posts

Posted - 2011-03-18 : 15:23:11

I have about 10 tables that all contain a batchId column. What is the most efficient way to get the total number of records in a batch based on the batchId?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 15:30:39

SELECT o.name [Table], i.rows
FROM sysobjects o
INNER JOIN sysindexes i on o.id=i.id
WHERE i.indid<2 and o.id in(SELECT id FROM syscolumns WHERE name='Batchid')

DazedAndConfused
Starting Member

5 Posts

Posted - 2011-03-18 : 15:40:53

Thanks for your reply. What if I want to get the count when BatchId = 5?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 15:56:38

You'll have to use SELECT Count(*) FROM myTable WHERE BatchID=5 for each table. Sysindexes only gives you the total row count for the table.

DazedAndConfused
Starting Member

5 Posts

Posted - 2011-03-18 : 16:15:24

So would I do something like this?
Select Count(*) FROM Table1 t1
Inner Join Table2 t2 on t2.BatchId = t1.BatchId
Inner Join Table3 t3 on t3.BatchId = t1.BatchId
....
Where t1.BatchId = 5

Is this the best way?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 16:22:47

Sorry, I misread your original question. You'll want to do:

SELECT Sum(Counts) FROM (
SELECT Count(*) Counts FROM Table1 WHERE BatchID=5
UNION ALL
SELECT Count(*) Counts FROM Table2 WHERE BatchID=5
UNION ALL
SELECT Count(*) Counts FROM Table3 WHERE BatchID=5
UNION ALL
SELECT Count(*) Counts FROM Table4 WHERE BatchID=5) a

You'll have to add each table with a UNION ALL between them.

DazedAndConfused
Starting Member

5 Posts

Posted - 2011-03-18 : 16:32:27

Sweet, Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources