question

paulhardy1 avatar image
paulhardy1 asked

How to create a "dynamic" view that checks for existence of a table to include in the UNION of that view

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

dynamicdynamic-management-viewdynamic creation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.