SQL Server Performance

insert-else-update

Discussion in 'SQL Server 2005 General Developer Questions' started by ammar_husain@yahoo.com, Jun 7, 2007.

  1. 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, company_profile_account=@p_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
  2. alzdba Member

    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) )




  3. Adriaan New Member

    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.
  4. alzdba Member

    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
  5. FrankKalis Moderator

  6. FrankKalis Moderator

  7. alzdba Member

    No problem --- Do we have a swap tool [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  8. Adriaan New Member

    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?
  9. FrankKalis Moderator

    <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=':D' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I don't think so. Guess the admin could edit the timestamp. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. FrankKalis Moderator

    <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=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  11. Madhivanan Moderator

    I like this type of syntax [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />UPSERT #MyTable<br />USING #MyStagingtable ON #MyStagingtable.myID = #MyTable.myID<br />WHEN MATCHED THEN myAnnotation = #MyStagingtable.myAnnotation<br />ELSE (#MyStagingtable.myID,#MyStagingtable.myAnnotation)<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page