I need some assistance on how to create a view that would be updated on a set schedule. (probably hourly.)
The client app I am working with generates temporary tables daily that are named with the prefix "M001948"
The view would something to the effect of:
CREATE VIEW V1 AS
SELECT * FROM M0019480001
SELECT * FROM M0019480002
SELECT * FROM M0019480003
I am not sure where to start, as I don't know the table names ahead of time. I know I can get a result set of the current tables with something like.
SELECT * FROM SYS.TABLES
NAME LIKE 'M001948%'
Thanks in advance for your assistance.