Concatenating tables into dynamic table names
I need to get results from a group of tables in 30 days.
The database seems to be creating new tables named monitorCounterLog with a date at the end, and then deleting anything beyond 30 days.
EG: dbo.monitorCounterLog20140903 is at my location today. They are then repeated for each date for 30 days.
EG:
dbo.monitorCounterLog20140903
dbo.monitorCounterLog20140902
dbo.monitorCounterLog20140901
dbo.monitorCounterLog20140831
dbo.monitorCounterLog20140830
Etc...
I need to achieve a similar result:
SELECT *
FROM monjtorCounterLog[30_days_worth_of_table_dates] ml
INNER
JOIN machNameTab mn
ON mn.agentGuid = ml.agentGuid
WHERE [stuff...]
To do this, you need to combine all the data in 30 tables.
I've been provided with some dynamic SQL (no experience with this) that works, but I don't know how to join it to other tables if needed.
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 to achieve joins outside of dynamic SQL?
Insert dynamic SQL into a temporary table then join the results or is there a better way to do this?
A bit lost in the correct syntax to use.
source to share
The variable @mCounterLog
in your code will contain the following:
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 are generating one-line code)
So, to join it with other tables, do this before passing the sp_execute variable:
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, a big UNION ALL query, become a sub query for your alias ml
source to share