[ Table join on dynamic table names ]

I need to retrieve results from a group of tables over a 30 days period.

The database seems to create new tables named monitorCounterLog with the date on the end, then delete anything outside of 30 days.

EG: dbo.monitorCounterLog20140903 for today's date in my location. These are then repeated for each date backwards for 30 days.

EG:
dbo.monitorCounterLog20140903
dbo.monitorCounterLog20140902
dbo.monitorCounterLog20140901
dbo.monitorCounterLog20140831
dbo.monitorCounterLog20140830

And so on...

I need to achieve similar to the following:

SELECT *

  FROM monjtorCounterLog[30_days_worth_of_table_dates] ml 
       INNER
       JOIN machNameTab mn
         ON mn.agentGuid = ml.agentGuid

 WHERE [stuff...]

This needs to union all data across the 30 tables.

I was given some dynamic SQL (no experience with this) which works, but I do not know how to join it to other tables as required.

    DECLARE @mCounterLog NVARCHAR(MAX)

    SELECT  @mCounterLog = STUFF((SELECT ' UNION ALL SELECT * FROM ' +st.name AS [text()]

      FROM  sys.tables st

     WHERE  (st.name LIKE 'monitorCounterLog[0-9]%' OR st.name = 'monitorCounterLog')
       FOR  XML PATH('')), 1, 11, '');

       EXEC sp_executesql @mCounterLog

What can I do here to achieve the joins outside the dynamic SQL required?

Insert the dynamic SQL into a temp table, then join on the results or is there a better way to go about this?

Little lost on the correct syntax to use.

Answer 1

The @mCounterLog variable in you code will contain this:

SELECT * FROM monitorCounterLog20140903 
UNION ALL
SELECT * FROM monitorCounterLog20140902
UNION ALL
SELECT * FROM monitorCounterLog20140901 
UNION ALL
SELECT * FROM monitorCounterLog20140831 
etc.

(I have inserted line breaks for readability. You code generates a one-liner)

So, to join it with the other tables do this before passing the variable to sp_execute:

SET @mCounterLog = N'SELECT * FROM (' + @mCounterLog
SET @mCounterLog = @mCounterLog + N') ml INNER JOIN achNameTab mn 
          ON mn.agentGuid = ml.agentGuid
          WHERE [stuff...]'
EXEC sp_executesql @mCounterLog

Basically you big UNION ALL query becomes a sub-query for your ml alias