Using Case to determine if table exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Case to determine if table exists

Author

Topic

Bjcascone
Starting Member

37 Posts

Posted - 2010-08-12 : 14:57:46

I am working on a Stored Proc that inserts some selected data from a set group of tables. the issue is that some times some of these tables do not exist. if the tables do not exist and the SP is run it will error out before completion. some of the script is hard coded. but here is what i have so far:

Case when [JcpDataOraclePoLines].[dbo].[PoLinesAll_Load] exists then

INSERT INTO Jcp..StratificationLoadTables

Select
2608,-- AS AuditId,
'POHeadersLines',
'LastUpdateDate',
'ExtAmt',
Year(A.LastUpdateDate) as YY,
Month(A.LastUpdateDate) as MM,
Day(A.LastUpdateDate) as DD,
Sum(CAST(ROUND(([UnitPrice] *[Quantity]), 2) as Money)) as Volume,
Sum(1) as Total
FROM JcpDataOraclePO.dbo.POHeadersAll_load A
JOIN [JcpDataOraclePoLines].[dbo].[PoLinesAll_Load] B
ON
A.PoHeaderId = B.PoHEaderId
Group By Year(A.LastUpdateDate), Month(A.LastUpdateDate),Day(A.LastUpdateDate)
Order By Year(A.LastUpdateDate), Month(A.LastUpdateDate),Day(A.LastUpdateDate)

Else

Basically i would like to preface this with an If Table exists clause but i am not sure how the syntax would go. any help or alternative ideas would be helpful

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-12 : 15:12:18

It won't work that way. The compiler won't allow it.

You'll need to wrap the insert into dynamic SQL to fake it out.

To check if it exists, you would do it like this:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SomeTableName'
BEGIN
...
END
ELSE
...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-12 : 15:12:39

But why don't you fix the database so that the table is there?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 15:29:22

it seems like it may be part of job batch in which one step is dropping tables and recreating later. then you need to make sure tables are available before it executes above rather than using IF EXISTS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bjcascone
Starting Member

37 Posts

Posted - 2010-08-12 : 16:11:11

The tables may or may not be in the database depending on the week. We get some data on a weekly basis and the table gets created in the DB and then later merged with another table and dropped. other data is obtained monthly and taken through the same process. but evey week we need to run this proc to stratify the data in the table to make sure we have the correct data in the table.

Author Topic
Bjcascone
Starting Member

USA
25 Posts
Posted - 08/12/2010 : 14:57:46
--------------------------------------------------------------------------------

I am working on a Stored Proc that inserts some selected data from a set group of tables. the issue is that some times some of these tables do not exist. if the tables do not exist and the SP is run it will error out before completion. some of the script is hard coded. but here is what i have so far:

Case when [JcpDataOraclePoLines].[dbo].[PoLinesAll_Load] exists then

INSERT INTO Jcp..StratificationLoadTables

Select
2608,-- AS AuditId,
'POHeadersLines',
'LastUpdateDate',
'ExtAmt',
Year(A.LastUpdateDate) as YY,
Month(A.LastUpdateDate) as MM,
Day(A.LastUpdateDate) as DD,
Sum(CAST(ROUND(([UnitPrice] *[Quantity]), 2) as Money)) as Volume,
Sum(1) as Total
FROM JcpDataOraclePO.dbo.POHeadersAll_load A
JOIN [JcpDataOraclePoLines].[dbo].[PoLinesAll_Load] B
ON
A.PoHeaderId = B.PoHEaderId
Group By Year(A.LastUpdateDate), Month(A.LastUpdateDate),Day(A.LastUpdateDate)
Order By Year(A.LastUpdateDate), Month(A.LastUpdateDate),Day(A.LastUpdateDate)

Else

Basically i would like to preface this with an If Table exists clause but i am not sure how the syntax would go. any help or alternative ideas would be helpful

Thanks

tkizer

I am going to try this method I think it may work. I will post back if i run into any issues.

Thanks for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-12 : 16:16:40

I posted the syntax already. But you'll need to use dynamic SQL for th INSERT part as that'll bomb the stored procedure if the table doesn't exist.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-12 : 16:34:45

[code]
if object_id('dbo.MyTable','U') is not null
begin
< execute dynamic SQL to insert data here >
end
[/code]

CODO ERGO SUM

Kokkula
Starting Member

41 Posts

Posted - 2010-08-13 : 01:11:04

Hello,

Code should be like below.

IF EXISTS (SELECT 1 FROM [JcpDataOraclePoLines].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PoLinesAll_Load'
BEGIN

//Your insert statement here

END
ELSE
BEGIN

//Your else part to be here

END

Hope its helpful....

Pavan
Infosys Technologies Limited

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-13 : 01:19:58

It actually should be "SELECT *" just like I posted, if you are going to use IF EXISTS approach that is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources