Insert into with where?? - 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)

Insert into with where??

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-28 : 23:31:49

Hello.
I'm trying to insert some values to a table but only if the drugid value is not already there.Can you help me?I assumed a where to give you an idea of what i need to do.

insert into drugsoperations(operationsid,drugid)
Values(3,5)
where drugsoperations.drugid not in(SELECT drugsoperations.drugid 
     FROM         dbo.Animals INNER JOIN 
 dbo.operations ON dbo.Animals.id = dbo.operations.animalid INNER JOIN 
     dbo.drugsoperations ON dbo.operations.id = dbo.drugsoperations.operationsid INNER JOIN
      dbo.drugs ON dbo.drugsoperations.drugid = dbo.drugs.id 
       where animals.id = 1 and drugsoperations.operationsid= 3)

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-29 : 00:28:40

i'm also using this.It gives me ok results (the table test has only one row because more rows gave me that same values on the number of rows) but i don't know if this is the right way.
The below without @variables for now.

INSERT INTO drugsoperations
(operationsid,drugid)
SELECT 3,7
FROM test
WHERE not exists (select drugsoperations.operationsid,drugs.id from dbo.Animals INNER JOIN
dbo.operations ON dbo.Animals.id = dbo.operations.animalid INNER JOIN
dbo.drugsoperations ON dbo.operations.id = dbo.drugsoperations.operationsid INNER JOIN
dbo.drugs ON dbo.drugsoperations.drugid = dbo.drugs.id
where drugsoperations.operationsid= 3 and drugs.id =7)

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 03:50:19

Should work fine WITHOUT the FROM clause I think?

INSERT INTO ...
SELECT ...
WHERE NOT EXISTS ...

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-29 : 11:14:48

So it's a right approach?Ok, i'll try it tonight in my development machine and will let you know.
Thank you very much.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-29 : 11:27:33

Hi, sorry one more question.Is it possible to have a concurrency issue with this approach?Is SQL locking the column on insert?Should i add something (p.e. timestamps?).
Thanks.

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 12:08:43

Its atomic, so I can't see any issue. What sort of problem do you envisage?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-29 : 20:45:45

First of all it works fine without the "From",thanks.
Second, the problem i have in mind is, if 2 users use insert the exact same time to insert the same data.Will it find that one of the users had inserted the row and not insert the 2nd user row?
If it will then i'm ok with this script.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-29 : 21:23:32

quote:
Originally posted by sapator

First of all it works fine without the "From",thanks.
Second, the problem i have in mind is, if 2 users use insert the exact same time to insert the same data.Will it find that one of the users had inserted the row and not insert the 2nd user row?
If it will then i'm ok with this script.

Yes

Check @@ERROR

I find it funny about 2 inserts from a man from Greece having a problem

EDIT: Even funnier when the query involves animals...please don't hate me

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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-30 : 01:26:56

@@error on the stored procedure?

P.S. No hatred :) .Ancient Greece and stuff right?Well it's a little different now.

Kristen
Test

22859 Posts

Posted - 2010-01-30 : 02:14:58

"if 2 users use insert the exact same time to insert the same data"

If the "insert" is Atomic the second one will wait. Hence the importance of making the Insert into a single command.
If the insert is


IF NOT EXISTS (SELECT * FROM ... WHERE ...)
BEGIN
    INSERT ...
END

then the combination of EXISTS and INSERT is not atomic (but it is possible to use Locking techniques to block a second user, and thus make it Atomic).

So ... both users could do NOT EXISTS at same time and both find the record does NOT exist, so both do the INSERT and then one of them waits (because the INSERT is atomic) and then the second one will fail.

"@@error on the stored procedure?"

The @@ERROR check is after EVERY statement, not just to check after the return of the SProc. so the SProc must do that after every statement [that matters], and then the Sproc can RETURN 0/non-zero to indicate OK / error [or return an error code some other way - OUTPUT parameter, or Resultset even]

There are other ways to do this:


UPDATE ...
IF @@ROWCOUNT = 0
BEGIN
    INSERT ...
END


INSERT ... WHERE NOT EXISTS ...
IF @@ROWCOUNT = 0
BEGIN
    UPDATE ...
END

each has different locking implications (for very busy systems), and each is better suited to different scenarios (lots of first-time inserts vs. lots of re-updating)

But that's assuming you want an "UpSert", rather than just a "Create new record, fail if already exists"

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-30 : 13:06:15

So the script we've been looking into from the beginning by your saying is safe,right?
I use it as is in the sp and not IF NOT EXISTS (SELECT * FROM ... WHERE ...)
BEGIN
INSERT ...
END

Also i have a @@error statement but i see that the executenonquery .net command will return the number of affected rows so i use it with this command but i guess it's useful in other SQL sp (probably if you want to update when a row has found to already exist).

Kristen
Test

22859 Posts

Posted - 2010-01-30 : 13:28:48

"IF NOT EXISTS (SELECT * FROM ... WHERE ...)
BEGIN
INSERT ...
END "

is NOT atomic. Two users could make the EXISTS at the same time and then both attempt the same INSERT, the second one will fail.

Therefore NOT safe.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-30 : 17:55:12

Yes that's what i've said
I use it as is(the script on the 2nd post without FROM) in the sp and NOT IF NOT EXISTS....Hope i understand correctly.
Thanks.

Kristen
Test

22859 Posts

Posted - 2010-01-31 : 04:46:49

"Yes that's what i've said "

Ok, I had misunderstood what you said, sorry about that

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-31 : 18:27:34

quote:
Originally posted by Kristen
Therefore NOT safe.

Unless you use protection

SELECT @Error = @@Error, @Rowcount = @@ROWCOUNT
IF @Error <> 0...

"Some say he could dance...."

Studebaker Hawk.....

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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-01-31 : 19:09:34

Ok but i don't want to make it too complicated i'm not an sql expert.

P.S. No problem Kristen, i'm not native English speaking so sometimes i can be hard to understand :)

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 06:42:28

"Unless you use protection

SELECT @Error = @@Error, @Rowcount = @@ROWCOUNT
IF @Error <> 0..."

Will give fatal error ("Duplicate PK") rather than return @@ERROR value, won't it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources