Record count - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Record count

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-11-22 : 09:57:00

I want to get a sum of record counts from several tables, names of which I get from a query. In other words, I have a bunch of "data tables", where I want to count reocrds, as well as a "data map table", which tells me which tables I need to count records in.

E.g.
declare @data_map table (id int, entity_id int, table_name varchar(30))
insert into @data_map
select 1, 501, 'data_table_a'
union all
select 2, 501, 'data_table_b'
union all
select 3, 502, 'data_table_a'

declare @data_table_a (event_id int, data_column_a int)
insert into @data_table_a
select 301, 42
union all
select 302, 43
union all
select 301, 95

declare @data_table_b (event_id int, data_column_b char(2))
insert into @data_table_b
select 301, 'aa'
union all
select 302, 'bb'
union all
select 304, 'cc'

declare @data_table_c (event_id int, data_column_b char(2))
insert into @data_table_c
select 302, 'ff'
union all
select 302, 'gg'
union all
select 305, 'hh'

I first need to get the list of tables from @data_map, for a given entity. And then record count from these tables, for a given event. For entity=501 and event=301 I'd expect to get back 3.

Is there any way to write a query or a SP that would return this, without using openquery?

Thanks for any ideas!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:10:58

why should you do it this way? why are you storing table names itself as a value in another table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ch9862
Yak Posting Veteran

76 Posts

Posted - 2011-11-22 : 10:41:56

quote:
Originally posted by visakh16

why should you do it this way? why are you storing table names itself as a value in another table?

That's how the database was designed eons ago :).

I don't have access to the entire system, but from what I've seen in one of the ASP pages, they read list of tables from the "data map table" into an array, and for each one execute a separate query to get the data. I don't need the actual data, only a record count. Any ideas on how to do it?

TIA!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:51:02

you need to use dynamic sql for this. look for syntax of sp_executesql below

http://msdn.microsoft.com/en-us/library/ms188001.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources