Need to Query Tables that are dynamically created

Question

We have an application that stores logged data in a Database called "ACManager" and Table called "Events_1".

When this table gets to a certain number of records the software creates another table called "Events_2". This continues as the data grows. I need to be able to query this data automatically as if it's all in one table without interference. Using a UNION will eventually create invalid querys when a new table is created dynamically by the application. Please also take into account performance.

So we need to Query as one table without UNION:

Select *
FROM ACManager.Events_1 , ACManager.Events_2 , ACManager.Events_xxxx(as needed)

score 0 · Answer 1 · answered Jul 30 '13 at 14:24

0

Use dynamic sql. try this

DECLARE @query VARCHAR(MAX)
SET @query='Select *
FROM SELECT STUFF((SELECT  '','' + name
        from sys.tables where name like ''Events%''
        FOR XML PATH('''')), 1, 1, '''') '

EXEC @query

answered Jul 30 '13 at 14:24

Nithesh Narayanan

11,481
34
98
138

Ideally, because these tables will be referenced in a myriad of reports, I'd like to create a "VIEW" I can query as needed. – user2634360 Jul 30 '13 at 14:31
Is there anything I need to customize in the Query above? It doesn't work. – user2634360 Jul 30 '13 at 14:35
try like this `EXEC sp_executesql @query` – Nithesh Narayanan Jul 30 '13 at 15:51

Aleksandr Fedorenko · Answer 2 · 2013-07-30T16:02:21.513

0

In this script you create procedure. In body of the procedure uses dynamic sql which build sql statement and then run this statement

CREATE PROCEDURE dbo.getEvents
AS
DECLARE @dml nvarchar(max) 
SELECT @dml = COALESCE(@dml + ' UNION ALL SELECT * FROM ', 'SELECT * FROM ') 
              + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) 
FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id                   
WHERE s.name = 'dbo' AND t.name LIKE 'event%'
--PRINT @dml
EXEC sp_executesql @dml

See demo on SQLFiddle

edited Jul 30 '13 at 16:02

answered Jul 30 '13 at 15:36

Aleksandr Fedorenko

16,594
6
37
44

Thank you. This works great. Is there a way to get this into a "VIEW" to simplify my reports? – user2634360 Jul 30 '13 at 15:47
You cannot use dynamic SQL from a VIEW, neither can you call stored procedures. Read this post http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure – Aleksandr Fedorenko Jul 30 '13 at 16:21

Need to Query Tables that are dynamically created

2 Answers2