[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Check if table exist before creating a constraint.

Posted on 2007-03-29
5
Medium Priority
?
534 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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

656 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