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 TRY
INSERT 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 [^] create table #Mytable(myID int not null, myAnnotation varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) create table #MyStagingtable(myID int not null, myAnnotation varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) Insert into #Mytable values (1,’test0′) Insert into #MyStagingtable values (1,’test1′) Insert into #MyStagingtable values (2,’test2′) MERGE INTO #MyTable USING #MyStagingtable ON #MyStagingtable.myID = #MyTable.myID WHEN MATCHED THEN UPDATE myAnnotation = #MyStagingtable.myAnnotation WHEN NOT MATCHED THEN INSERT VALUES (#MyStagingtable.myID, #MyStagingtable.myAnnotation) Select * 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=’

‘ />]

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">quote:<hr height="1" noshade id="quote">Originally posted by alzdba No problem — Do we have a swap tool [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> I don’t think so. Guess the admin could edit the timestamp. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan 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? <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

I like this type of syntax [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] UPSERT #MyTable USING #MyStagingtable ON #MyStagingtable.myID = #MyTable.myID WHEN MATCHED THEN myAnnotation = #MyStagingtable.myAnnotation ELSE (#MyStagingtable.myID,#MyStagingtable.myAnnotation) Madhivanan Failing to plan is Planning to fail

]]>