insert where exists stored procedure

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

insert where exists stored procedure

Author

Topic

j4ydh
Starting Member

18 Posts

Posted - 2010-08-03 : 04:23:30

Hi

I am using the following sproc to essentially update a table over time. The issue is that I am inserting the items that have been found in a directory which can include items already listed in the table.

@name varchar (50),
@DT smalldatetime,
@cam int = 1

AS

insert into t_1(name,DT,cam)
select name, DT, cam
from t_1
where not exists (select name, DT, cam from t_1
where t_1.name = @name)

Unfortunately the above sproc doesn't feel very stable.
First issue is that if the table is empty it won't insert at all and I am concerned that this isn't the most robust way of approaching the situation.

Summary:
Sproc should check if the name already exists (@name 12 digit number - currently as string) then insert if not a duplication).

Any help would be appreciated

Regards
J

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 04:26:24

[code]
if ((select count(*) from t_1
where t_1.name = @name)=0)
insert into t_1(name,DT,cam)
select name, DT, cam
from t_1
where t_1.name = @name
[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 05:11:20

quote:
Originally posted by visakh16

if ((select count(*) from t_1
where t_1.name = @name)=0)
insert into t_1(name,DT,cam)
select name, DT, cam
from t_1
where t_1.name = @name

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

or

if not exists(select * from t_1 where t_1.name = @name)
insert into t_1(name,DT,cam)
select name, DT, cam
from t_1
where t_1.name = @name

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-08-03 : 05:32:25

IF COUNT(*)=0 ... INSERT
and
IF NOT EXISTS ... INSERT

are not good routes guys, surely?

plus NAME is supposed to be unique - so is never going to work, surely?

perhaps this is what O/P meant?


insert into t_1(name,DT,cam)
select name=@name, DT=@DT, cam=@cam
from t_1
where not exists (select name, DT, cam * from t_1
where t_1.name = @name)

j4ydh
Starting Member

18 Posts

Posted - 2010-08-03 : 05:36:26

Hi

Thank you for the help.
When the table starts without any data the sproc doesn't add any new inserts.

Is this expected? I was presuming that the if exist would note that if nothing is in the table it would insert data.

Kind Regards
Jay

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-08-03 : 05:52:54

quote:
Originally posted by j4ydh

When the table starts without any data the sproc doesn't add any new inserts.

It will not if you select from a table with no rows.

Use the t-sql syntax without the FROM clause and increase the isolation of the check to stop key violations/duplicates.


INSERT INTO t_1([name] ,DT ,cam)
SELECT @name, @DT, @cam
WHERE NOT EXISTS
(
	SELECT *
	FROM  t_1 T1 WITH (SERIALIZABLE) -- apply key range lock
	WHERE T1.[name] = @name
)

j4ydh
Starting Member

18 Posts

Posted - 2010-08-03 : 06:16:33

Thank you everone for the help. Really appreciated.
Ifor supplied the version that worked for my situation.

Is there any more info that I can study to show the importance of the key range lock as I am aware of this but not completely understanding the role it plays in my situation.

Thanks again everyone.

J

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-08-03 : 06:55:23

The key range lock will stop the case where two concurrent processes do not find [name] = @name in the table and then both try to add it.

You should try and read up on concurrency in BOL although it is not particularly well explained.

The only books I have seen on concurrency are thick, theoretical and go well beyond databases.

Does anyone know of a book that does a good job of explaining concurrency in SQL Server? (I still have to wrap a damp towel around my head when considering snapshot isolation!)

Kristen
Test

22859 Posts

Posted - 2010-08-03 : 08:37:09

quote:
Originally posted by j4ydh

Is there any more info that I can study to show the importance of the key range lock as I am aware of this but not completely understanding the role it plays in my situation.

We don't bother with it. Likelihood of a duplicate on exactly the same PK is so rare as to be virtually non-existent in our applications.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources