question

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?