inserting/updating - 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

General SQL Server Forums

New to SQL Server Programming

inserting/updating

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-02-01 : 13:08:32

I am looping through some code, written in vb.net to insert variables into sql.

When executing the code again (to check more values), i am running into problems updating the values that already exist (the primary key).

Is there any easy way to insert/update together, like if the primary key doesn't exist insert the row of data, but if it does exist, update the row of data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 13:10:44

yup, you can do like


if not exists (select 1 from table where field1=@value1 and field2=@value2...)
 --insert code

else

-- update code

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 13:12:10

[code]
INSERT INTO MyTable(Col1, Col2, ...)
SELECT 'value1', 'value2', ...
WHERE NOT EXISTS (SELECT * FROM MyTable WHERE SomePK = 'MyPKValue')
IF @@ROWCOUNT = 0
BEGIN
UPDATE MyTable
SET Col1 = 'Value1', Col2 = 'Value2' ...
WHERE SomePK = 'MyPKValue'
END
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 13:12:41

or even


update table
set field1 = @value1,
field2=value2
..
where keyval=@value

if @@rowcount=0 then
 --insert code

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 13:13:14

@Visakh : Risk (in high throughput / contention DBs) that another user does the Insert between the EXISTS test and the INSERT made by the first user ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 13:16:08

quote:
Originally posted by Kristen

@Visakh : Risk (in high throughput / contention DBs) that another user does the Insert between the EXISTS test and the INSERT made by the first user ?

yup thats true

what about doing this inside transaction?

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 13:19:12

@Visakh : I think the UPDATE .. IF @@ROWCOUNT=0 suffers from the same problem, another user could INSERT in that timeframe (or is the virtual-record for the proposed PK-insert locked? (I don't know the answer to that).

The INSERT ... WHERE NOT EXISTS ... IF @@ROWCOUNT = 0 ... UPDATE route only fails if the record is DELETEd by another user - and I work on the basis that today's system never delete anything, they store it forever

... but it does have that weakness

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 13:24:39

"what about doing this inside transaction?"

Yes, could do (should do, in fact, so that IF @@ERROR <> 0 then a ROLLBACK is possible), but its relatively safe "as-is" I think ... and thus generally suitable for the sort of relatively a) inexperienced and b) not-mega-huge-volume-inserts question-askers that we get here.

Its just my personal opinion, but I think there is a risk that normal answers [to this quite frequently asked question] assume knowledge/experience, and the risk is that the recipient follows it, sees a DEADLOCK VICTIM once in a blue-moon, has no ability to reproduce it, would like to avoid the "annoyed-user" but doesn't' know how

But the whole UpSert debate is one of the Great Religious Wars

Maybe we should all be using the MERGE command for this scenario now ... (I'm not a fan of MERGE though ...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources