I would like to have a view that checks for the existence of a table and then add that table to the UNION of tables.
Currently, I can create this view:
create view dbo.GL_FYS as select * from dbo.GL_2019 union select * from dbo.GL_2018
end
In March of 2020, dbo.GL_2020 will exist.
I would like the view to detect the existence of dbo.GL_2020 and drop off the old one to have a view like this:
create view dbo.GL_FYS as select * from dbo.GL_2020 union select * from dbo.GL_2019 end
I have a procedure to detect the existence of the new table, based on whether or not it exists build the create view string and use EXECUTE (string). But this procedure would have to run every day to detect the new table.
Can this all be done inside a CREATE VIEW statement?
Thank you for any help that you could give me.
paul