none
Using a query to loop through tables that are similar in structure but have different names(MyTablejuly2012,MyTableJune2012,MyTableAug2012) RRS feed

  • Question

  • I would like to generate report like below. Because after every month we have existing structure where data goes to archive tables. User can request data from current as well as from historic data. For current we have name like MyTable. But once current month finishes on 1st day of new month this data is moving to Mytable[PreviousMonthYear] table.

    SELECT .. FROM  MytableDEC2011 where...
    UNION
    SELECT .. FROM  MytableJ2012 where...
    UNION
    SELECT .. FROM  MytableFeb2012where...
    UNION
    SELECT .. FROM  MytableMar2012where...

    Monday, September 10, 2012 7:24 AM

All replies

  • Hi Deepak,

    I belive the follwoing query will fulfill u r req.

    Set Nocount on
    Create table ##Mytable (name varchar(50))  /*make sure the schema should match u r history table*/

    Declare @DymSQL as Varchar(200)
    Declare @Tabname as varchar(50)
    Declare Sampcursor cursor   for  Select name from sys.objects  where name like 'Mytable%' and type = 'U'
    Open Sampcursor
    Fetch from Sampcursor into @Tabname
    While (@@FETCH_STATUS = 0)
    Begin
    Set @DymSQL = 'Insert into ##Mytable Select Appname from '+ @Tabname
    --print @DymSQL
    Exec(@DymSQL)
    Fetch Next from Sampcursor into @Tabname
    End
    Close Sampcursor
    Deallocate Sampcursor
    Select * from ##Mytable
    Drop table ##Mytable


    Thanks, Vasantha Prabakaran

    • Marked as answer by t_deepak Monday, September 10, 2012 9:33 AM
    • Unmarked as answer by t_deepak Monday, September 10, 2012 9:36 AM
    Monday, September 10, 2012 9:17 AM
  • Actually there are various cases for month selection. Eg There are Mytable is having records for current month. And historic tables will have name like MytableAug2012, MytableJul2012. So user will have option to select start date as 15 April2012 to 7 Sept 2012. For this query should return data from following tables: Mytable[ for september it will have records from 1-7 sept only], MytableAug2012, MytableJul2012, MytableJun2012, MytableMay2012, MytableApril2012[from15 April onwords]
    Also. I have list of previous years tables also. But I want to include the records from tables that are requested by user. So other tables needs to be skipped in that case.
    • Edited by t_deepak Monday, September 10, 2012 9:53 AM
    Monday, September 10, 2012 9:43 AM
  • Hai Deepak pleas include the below logic,

    While (@@FETCH_STATUS = 0)
     Begin
     IF((Convert(date,'01'+Replace(@Tabname,'Mytable','')) > = @UserFromdAte) and (Convert(date,Left(@UserTodAte,2)+Replace(@Tabname,'Mytable',''))  =< @UserTodAte))
     Begin
     Set @DymSQL = 'Insert into ##Mytable Select Appname from '+ @Tabname +' Where [datecolumn] between @UserFromdAte and @UserTodAte'
     --print @DymSQL
     Exec(@DymSQL)
     End
     Fetch Next from Sampcursor into @Tabname
    End


    Thanks, Vasantha Prabakaran

    Monday, September 10, 2012 10:28 AM
  • You should really redesign this structure into a partitioned table instead of individual tables.  This is extremely difficult to manage.

    Monday, September 10, 2012 8:37 PM
    Moderator