Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
same query on list of tables

Answered same query on list of tables

  • Monday, February 04, 2013 5:03 PM
     
     
    Sign In to Vote
    0
    

    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. 

    Thanks 

    --

    Nikhil

     
     

All Replies

  • Monday, February 04, 2013 5:25 PM
     
     
    Sign In to Vote
    0

    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 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    You can apply a dynamic SQL loop through the tables:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    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:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    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




     
  • Monday, February 04, 2013 5:28 PM
     
      Has Code
    Sign In to Vote
    0
     
    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 
END
    Try 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
     
     
    Sign In to Vote
    0

    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 PM
     
     
    Sign In to Vote
    0
    thanks 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