Monday, February 04, 2013 5:03 PM
I have a list of tables stored in a temporary table, and i need to run the same query on all the tables in table_name column and get relevant values for those tables, my relevant values are max time and min time in the same column 'TS' stored in all the tables so my result set should look like
And if it was only one table i could just run the select statement on that table but i have more than hundred of these tables and need to get the same stored procedure which returns these values . Would appreciate some guidance on how to proceed on this.
Monday, February 04, 2013 5:25 PM
Why do you have the same data in different tables? Can you combine these tables into one table?
It looks the tables are in different databases. If you can't combine the tables into one, can you write a procedure that compiles the data from the different databases into one database and table for your reporting purposes?
You can write one query with many UNION statements: http://msdn.microsoft.com/en-us/library/ms180026.aspx
In the past, I have written a query that writes the query, by pasting the results into another query:SELECT
'UNION ALL SELECT MIN(xTime), MAX(xTime) FROM ' + t.name
FROM sys.tables t
WHERE t.type = 'U'
Monday, February 04, 2013 5:26 PMModerator
You can apply a dynamic SQL loop through the tables:
See how a cursor used in the "Loop through all databases to get table count" example.
Similarly, cursor is used with dynamic SQL to REBUILD indexes in all tables:
You can accumulate the data in a @tablevariable or a #temptable.
Kalman Toth SQL 2008 GRAND SLAM
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, February 04, 2013 5:30 PM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, February 04, 2013 5:33 PM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Monday, February 04, 2013 5:34 PM
- Marked As Answer by Nikhil_NK Monday, February 04, 2013 10:23 PM
Monday, February 04, 2013 5:28 PM
declare @imax as int DECLARE @1 AS INT select table_name from TABLE_Y set @imax=@@rowcount WHILE (@1 <=@imax) BEGIN DECLARE @SQ AS date, @SX as date, @idd as int SELECT TOP 1 @idd=id, @SQ=min(date??), @SX=max(date??) FROM TABLE??? WHERE BUP=0 ORDER BY ?? DESC UPDATE TABLE_Y SET mintime=@SQ, maxtime=@SX, BUP=1 WHERE id=@idd SET @1 = @1 + 1 ENDTry this. I put a lot of ? when I dunno the name of the table and column...look: BUP is a new column you can using to check if the row is updated or not..
- Edited by DIEGOCTN Monday, February 04, 2013 5:29 PM
Monday, February 04, 2013 6:44 PM
Thanks for the reply Rogge, I have different data in all the tables, the structure of columns is also different only one column name is common and that's the timestamp column 'TS'. And yes the tables are in different databases and i can't really get them all into one data base, and i am not sure why they were in different databases .
and i tried to execute your query with the temp table I have and i get queries as result i am not sure how to execute all of them to get the result set i need
Monday, February 04, 2013 8:36 PMthanks for the reply Diegoctn , in the above table i can't really use Table??? as a variable for the table name coming from the temptable is there a way i can do it if i can do that i think it willl solve my problem