question

Mandar Alawani avatar image
Mandar Alawani asked

need to SUM from various dynamic tables

Hi All, below is the code snippet.. I am trying to select rows from different tables...where table names will be provided dynamically (based on values stored in a table variable and running in a loop) --WITHOUT sum SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @tables TABLE ( fID int identity(1,1) NOT NULL, fTablename varchar(100) NOT NULL PRIMARY KEY CLUSTERED ); insert into @tables (fTablename) select [name] FROM DBname.sys.tables where type = 'u' and name like 'table[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and name >= 'table120130501' and name <= 'table1020130510' --DECLARE @sumtable TABLE ( -- fID varchar(100) NOT NULL PRIMARY KEY CLUSTERED, -- fCount int -- ); --select * from @tables DECLARE @tablename varchar(100),@id int,@count int, @SQL varchar(2000),@sum bigint SET @id = 1 select @count = count (*) from @tables WHILE (@id <= @count) BEGIN select @tablename = fTablename from @tables where fID = @id print @tablename set @SQL = 'select id,count(*) as [count] from ' + @tablename + ' where id in (''101'',''102'',''103') group by id ' --print @SQL exec (@SQL) SET @id = @id + 1 END --------------- when the run the above code, i am able to cycle thru all the tables and get individual results..however, I need to do a SUM of the count from each of these tables for each id is there a way i can store each of the select in a temp table/table variable one below the other (like append) and then run a sum query on it?
t-sqldynamic-sqlcountsum
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
No need to do that with dynamic sql select name, row_count from sys.dm_db_partition_stats sps join sys.tables st on sps.[object_id] = st.[object_id] where name like 'table[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and name >= 'table120130501' and name ]]= 'table120130501' and name
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.