[ 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