SQL Server Performance Forum – Threads Archive
insert-else-update
Hi, I am trying to insert a row and if the operation fails dues to a unique key violation the query should fail. If the operation fails due to primary key violation the query should perform an update. I am trying to handle this using try-catch of sql 2005 as – BEGIN TRYINSERT INTO [table1](
property,
company_profile_account,
one_person_rate,
two_person_rate,
extra_person_rate)
VALUES (@p_property,
@p_company_profile_account,
@p_one_person_rate,
@p_two_person_rate,
@p_extra_person_rate
)
END TRY
BEGIN CATCH
SET @ErrorNumber = ERROR_NUMBER()
IF (@ErrorNumber = 2601 )
BEGIN
begin try
UPDATE clc_configuration set property= @p_property, [email protected]_company_profile_account,
one_person_rate = @p_one_person_rate, two_person_rate = @p_two_person_rate, extra_person_rate = @p_extra_person_rate;
end try
begin catch
SET @ErrorNumber = ERROR_NUMBER()
end catch
END
else if (@ErrorNumber = 2627)
begin
return
end END CATCH SET NOCOUNT OFF Return @ErrorNumber My questions: 1) Is this way of insert-else-update is faster than the traditional "if exist(select…" solution? 2) How portable the script is when it comes to error number. Can error number change in future SQL sever versions? Should I depend on checking their no. in my programming?
Thanks,
Regards,
Ammar
you might be able to perform one update stmt and insert statement, to fullfil the whole operation sequence…
e.g . — Start with update
update T
set col1 = T.col1,….. from yourtable T
inner join yournewdatatable N
on T.key(s) = N.key(s) — insert new rows
Insert into yourtable (……)
select N.col, ….
from yournewdatatable N
let join yourtable T
on T.key(s) = N.key(s)
where T.key is null — or
Insert into yourtable (……)
select N.col, ….
from yournewdatatable N
where not exists (select * from yourtable T where T.key(s) = N.key(s) )
Error handling is for things that are less predictable than key violations. You know beforehand that there will be key violations, so use the UPDATE and INSERT queries posted by alzdba. There have been rumours that future versions of SQL Server may support "upsert" queries.
Indeed this is still futuristic [^]<br /><br />create table #Mytable(myID int not null, myAnnotation varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> )<br />create table #MyStagingtable(myID int not null, myAnnotation varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> )<br /><br />Insert into #Mytable values (1,’test0′)<br /><br />Insert into #MyStagingtable values (1,’test1′)<br />Insert into #MyStagingtable values (2,’test2′)<br /><br /><br />MERGE INTO #MyTable<br />USING #MyStagingtable<br />ON #MyStagingtable.myID = #MyTable.myID<br />WHEN MATCHED THEN<br />UPDATE myAnnotation = #MyStagingtable.myAnnotation<br />WHEN NOT MATCHED THEN<br />INSERT VALUES (#MyStagingtable.myID,<br />#MyStagingtable.myAnnotation)<br /><br />Select *<br />from #MyTable
Correct! Though it is likely to be called MERGE. Check out page 8 onhttp://download.microsoft.com/downl…bc9e-4168b65aaa71/SQL2008_ProductOverview.doc —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Oops, my posting was aimed at Adriaan’s reply. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
No problem — Do we have a swap tool [<img src=’/community/emoticons/emotion-2.gif’ alt=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
Lots of keywords there, and they all make sense. But to me it doesn’t really look like you’re writing less code than with UPDATE and INSERT. The use of a VALUES list suggests perhaps an underlying cursor operation?
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />No problem — Do we have a swap tool [<img src=’/community/emoticons/emotion-2.gif’ alt=’
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Lots of keywords there, and they all make sense. But to me it doesn’t really look like you’re writing less code than with UPDATE and INSERT.<br /><br />The use of a VALUES list suggests perhaps an underlying cursor operation?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I’m tempted to say it is too early to speculate how the final syntax will look like. [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
I like this type of syntax [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>