• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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?


0
halifaxman
Asked:
halifaxman
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, that is not possible.
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...
0
 
jamesguCommented:
run this to get your statement,



declare @sql varchar(1000)
begin
set @sql = ''
if exists( select 1 from sysobjects where name = 'out_jan08' and type = 'U')
begin
	select @sql = 'select etc from out_jan08 where ... '
end;
 
if exists( select 1 from sysobjects where name = 'out_feb08' and type = 'U')
begin
	if ( @sql != '' )
	begin
		select @sql = @sql + char(10) + 'union' + char(10)
	end;
	select @sql = @sql + 'select etc from out_feb08 where ... '
end;
 
print @sql
end;

Open in new window

0
 
BrandonGalderisiCommented:
The other way I can think of is....

--assuming data types here...
create table #Out (etc nvarchar(1000))

if object_id('dbo.out_jan08') is not null
 exec ('insert into #out select etc from out_jan08')
 
if object_id('dbo.out_feb08') is not null
 exec ('insert into #out select etc from out_feb08')

select * from #out

drop table #out
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now