Solved

Check if table exist before creating a constraint.

Posted on 2007-03-29
5
513 Views
Last Modified: 2012-06-27
ALTER TABLE #Tmp_LN_Discount ADD
            CONSTRAINT PK_Tmp_LN_Discount PRIMARY KEY NONCLUSTERED
            (      STR_ID,
                  RGST_ID,
                  TRAN_ID,
                  TRAN_LN_NUM,
                  DISC_CD            )  ON [PRIMARY]

Check if table exist before creating a constraint.

There is already an object named 'PK_Tmp_LN_Discount' in the database.
0
Comment
Question by:mathieu_cupryk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18819057
if exists (select name from sysobjects where name like '#tmp_ln_discount%')
begin
     drop table #tmp+ln_discount
end
create table...
add constraint...

0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18819082
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Tmp_LN_Discount]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE #Tmp_LN_Discount ADD
            CONSTRAINT PK_Tmp_LN_Discount PRIMARY KEY NONCLUSTERED
            (      STR_ID,
                  RGST_ID,
                  TRAN_ID,
                  TRAN_LN_NUM,
                  DISC_CD            )  ON [PRIMARY]
GO
0
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 total points
ID: 18819100
You might not want to drop the table first. It may contain data.

This is more correct. My cut and past went crazy:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Tmp_LN_Discount]')
ALTER TABLE #Tmp_LN_Discount ADD
            CONSTRAINT PK_Tmp_LN_Discount PRIMARY KEY NONCLUSTERED
            (      STR_ID,
                  RGST_ID,
                  TRAN_ID,
                  TRAN_LN_NUM,
                  DISC_CD            )  ON [PRIMARY]
GO
0
 

Author Comment

by:mathieu_cupryk
ID: 18819164
rboy
what about CREATE TABLE #Tmp_LN_Discount (STR_ID INT NOT NULL, RGST_ID INT NOT NULL, TRAN_ID INT NOT NULL, TRAN_LN_NUM SMALLINT NOT NULL, DISC_CD INT NOT NULL, DISC_AMT MONEY NULL)
      
There is already an object named '#Tmp_LN_Discount' in the database.
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18819224
I was looking only at the contraint portion of the command. You can do the same type of query:

if exists (select name from sysobjects where name like '#tmp_ln_discount%')
begin
     drop table #tmp+ln_discount
end

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question