how to code if not exists(then set identity and then insert

Question

Post reply

how to code if not exists(then set identity and then insert

anushaun

SSC Enthusiast

Points: 124
More actions
April 8, 2010 at 12:13 pm

#220297

Hi, I am very new to SQL SErver. Have been coding ORacle and want some help with this. What I am basically trying to do is-
If a value doesn't already exist, then set identity on and insert, else, set identity off and insert. SQL server doesn't like it when i tried to set the identity in the condition. How can I achieve this? This is urgent and any help is appreciated.
This is the query I used -
if not exists (select campaigncommentid from CampaignStagingComment where campaigncommentid in (select CampaignCommentID from CampaignComment cc INNER JOIN campaignstorerun cs ON (cc.campaignid = cs.camapignid))) (set identity_insert CampaignStagingComment on insert CampaignStagingComment (..,..,..) select * from CampaignComment cc where exists (select 1 from campaignstorerun ctrr where cc.campaignid = ctrr.campaignid) and not exists (Select 1 from CampaignStagingComment cs where cc.campaignid = cs.campaignid) set identity_insert CampaignStagingComment off)
else (set identity_insert CampaignStagingComment off insert CampaignStagingComment (..,..,..) select * from CampaignComment cc where exists (select 1 from campaignstorerun ctrr where cc.campaignid = ctrr.campaignid) and not exists (Select 1 from CampaignStagingComment cs where cc.campaignid = cs.campaignid) set identity_insert CampaignStagingComment on)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

would you mind in formatting your query properly? pretty please?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

One quick thing, you're not formatting your conditional statement correctly ...

IF blah blah

BEGIN

<do this>

<do that>

END

ELSE

BEGIN

<do some other thing>

<do one last thing>

END

Posting your code in a more readable format and including complete table definitions will help too.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

anushaun SSC Enthusiast Points: 124 More actions · Answer 3

anushaun

SSC Enthusiast

Points: 124

April 8, 2010 at 2:56 pm

#1148666

Thanks so much. 🙂

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

As a side bar, it sounds like you may be trying to keep a "gapless" IDENTITY column. That's normally a highly counter productive thing to do. Please explain why your doing this. And don't just say "business requirements", please. What is the logic behind it all?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)