0 votes
1 view
in SQL by (22.4k points)

Assume a table structure of MyTable(KEY, datafield1, datafield2...).

Often I want to either update an existing record or insert a new record if it doesn't exist.

Essentially:

IF (key exists)

run update command

ELSE

run insert command

What's the best performing way to write this?

1 Answer

0 votes
by (40.3k points)
edited by

Try using the below code:

begintran

updateMytable with (serializable) set ...

where key1 = @key1

if @@row_count = 0

begin

insert into Mytable (key1, ...) values (@key1,..)

end

committran

Otherwise, use this:

begintran

if exists (select * from Mytable with (updlock,serializable) where key1 = @key1)

begin

updateMytable set ...

where key1 = @key1

end

else

begin

insert into table (key1, ...)

values (@key1, ...)

end

committran

Related questions

0 votes
1 answer
0 votes
1 answer
+2 votes
1 answer
+1 vote
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...