if exist - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

if exist

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2009-08-11 : 12:17:23

I want to create stored procedure where I want to insert record. But, I need first check does record exist.
If does not exist then insert record.
I already created this ...

begin
DECLARE @X Nchar(10)
SET @X = 0
SELECT @X=Field1 from Table1
if @X=0
insert ...

end

Is there something differentj ...? For some reason I do not like it.

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-11 : 12:25:35

IF NOT EXISTS (SELECT * FROM....)
INSERT
ELSE
UPDATE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-11 : 15:26:19

Another alternative:

UPDATE
IF @@ROWCOUNT = 0
BEGIN
	INSERT
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-11 : 16:17:07

It wasn't entirely clear to me if you want to check for one specific row, the one you want to insert, or to check for any rows. Assuming it is one specific row or any number of specific rows for that matter perhaps this.

If you are in a multi-user environment then it is possible that between the test for rows and the insert someone else could add the row(s) you want to add. In that case maybe use a LEFT OUTER JOIN checking left table for null:

insert <targetTable> (colList)
select (colList)
from <sourceTable> s
left outer join <targetTable> t on t.<PK> = s.<PK>
where t.<PK> is null

Be One with the Optimizer
TG

ilimax
Posting Yak Master

164 Posts

Posted - 2009-08-11 : 16:34:29

Yes, I am checking for specific record and if it is ther I update, if not I insert new ...

Thank you guys for all responses ... I will review all of these ...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-11 : 16:38:35

quote:
Originally posted by ilimax

Yes, I am checking for specific record and if it is ther I update, if not I insert new ...

Thank you guys for all responses ... I will review all of these ...

In that case you do the UPDATE first then the INSERT with the left outer join. That works for one or many rows. The update can only work in rows that are already there. Then the insert will only insert the missing rows. No need to check anything.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources