Mark Wilson
asked on
If Table Exists
On the first day of every month we create a table of data for the previous month, I then join them in a union statement as below.
select etc
from out_jan08
where
union all
select etc
from out_feb08
where
On the first day of every month I add the next table to the union statement, so on the 1st Novermber I will add out_oct08 to the statement.
I am wandering if there is away where I can add all 12 months to the stement and add an if table exists statement, so the query will still run even if the table does not exist.
i.e.
union all
if exists
select etc
from out_oct08
where etc
Is this possible?
select etc
from out_jan08
where
union all
select etc
from out_feb08
where
On the first day of every month I add the next table to the union statement, so on the 1st Novermber I will add out_oct08 to the statement.
I am wandering if there is away where I can add all 12 months to the stement and add an if table exists statement, so the query will still run even if the table does not exist.
i.e.
union all
if exists
select etc
from out_oct08
where etc
Is this possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The other way I can think of is....
--assuming data types here...
create table #Out (etc nvarchar(1000))
if object_id('dbo.out_jan08')
exec ('insert into #out select etc from out_jan08')
if object_id('dbo.out_feb08')
exec ('insert into #out select etc from out_feb08')
select * from #out
drop table #out
--assuming data types here...
create table #Out (etc nvarchar(1000))
if object_id('dbo.out_jan08')
exec ('insert into #out select etc from out_jan08')
if object_id('dbo.out_feb08')
exec ('insert into #out select etc from out_feb08')
select * from #out
drop table #out
you have either to ensure all 12 tables exist at all times,
or simply create, assuming you are using a database that supports partitioned tables, those instead of 1 table per month...