SQL Server Performance
  1. dineshasanka Moderator

    Declare @Code varchar(3)

    Declare @Description varchar(15)

    Set @Code = 'ABC'

    Set @Description ='Merge'


    Merge into tblData T

    USING TblData S ON S.Code = @Code

    WHEN MATCHED THEN UPDATE

    SET t.Description = @Description

    WHEN NOT MATCHED THEN INSERT (Code,Description)

    VALUES (@Code,@Description);

    select * from tbldata



    tblData table does not have records. If I run above command I should get one record. However, I getting no records. Is above statment is incorrect.





    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  2. satya Moderator

  3. thomas New Member

  4. Adriaan New Member

    MERGE ... USING ... seems to work with existing rows between existing tables, not with a list of variables as the source.

    Perhaps if you convert the list of variables to a derived table with aliases ---

    MERGE INTO tblData T
    USING (SELECT @Code Code, @Description Description) S ON T.Code = S.Code
    WHEN MATCHED THEN UPDATE
    SET t.Description = s.Description
    WHEN NOT MATCHED THEN INSERT (Code,Description)
    VALUES (Code, Description);

    I'm still not convinced that we're writing less code with this syntax ...
  5. Adriaan New Member

    For the record:

    IF EXISTS (SELECT * FROM tblData WHERE Code = @Code)
    UPDATE tblData SET Description = @Description WHERE Code = @Code
    ELSE
    INSERT INTO tblData (Code, Description) VALUES (@Code, @Description)
  6. dineshasanka Moderator

    I just wanted to get hands on expereince on MERGE

    quote:Originally posted by Adriaan

    For the record:

    IF EXISTS (SELECT * FROM tblData WHERE Code = @Code)
    UPDATE tblData SET Description = @Description WHERE Code = @Code
    ELSE
    INSERT INTO tblData (Code, Description) VALUES (@Code, @Description)

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  7. Adriaan New Member

    Yes, but did you test the script that I posted before that?<br /><br />Like I stated, the second script was "for the record".[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  8. thomas New Member

    check out the link i posted, Dinesh, it works a treat.
  9. dineshasanka Moderator

    quote:Originally posted by Adriaan

    Yes, but did you test the script that I posted before that?



    I will check that tonight and let you know allong with other scripts. thanks guys


    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  10. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by thomas</i><br /><br />check out the link i posted, Dinesh, it works a treat.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">The problem presented by Dinesh is that he wants to merge a value list with rows in one table, rather than merging rows between two tables. Neither of the two links seem to cover that scenario.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  11. dineshasanka Moderator

    quote:Originally posted by Adriaan

    MERGE ... USING ... seems to work with existing rows between existing tables, not with a list of variables as the source.

    Perhaps if you convert the list of variables to a derived table with aliases ---

    MERGE INTO tblData T
    USING (SELECT @Code Code, @Description Description) S ON T.Code = S.Code
    WHEN MATCHED THEN UPDATE
    SET t.Description = s.Description
    WHEN NOT MATCHED THEN INSERT (Code,Description)
    VALUES (Code, Description);

    I'm still not convinced that we're writing less code with this syntax ...
    Yes it is working

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  12. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br />Yes it is working<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">See how far a little lack of training will get you. I should consider answering questions about Oracle syntax now.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  13. MohammedU New Member

    <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 />MERGE ... USING ... seems to work with existing rows between existing tables, not with a list of variables as the source.<br /><br />Perhaps if you convert the list of variables to a derived table with aliases ---<br /><br />MERGE INTO tblData T<br />USING <font color="blue">(SELECT @Code Code, @Description Description) S ON T.Code = S.Code</font id="blue"><br />WHEN MATCHED THEN UPDATE <br />SET t.Description = s.Description<br />WHEN NOT MATCHED THEN INSERT (Code,Description)<br />VALUES (Code, Description);<br /><br />I'm still not convinced that we're writing less code with this syntax ...<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I don't think we are writing less code but may be performance gain [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Syntax looks like plain enlish....<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  14. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MohammedU</i><br />I don't think we are writing less code but may be performance gain [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Syntax looks like plain enlish....<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Under the hood, I'm pretty sure the SQL engine expands it to a set of insert, update and delete queries to ensure the basic data integrity. It would be interesting to see a trace of that.<br /><br />And if you call that plain English, you must be a computer geek.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  15. satya Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] LOL, thats a good laugh Adriaan.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  16. MohammedU New Member

    The following sentences looks to me plain Enlish...<br />I am not a computer geek [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />WHEN MATCHED THEN UPDATE <br /><br />WHEN NOT MATCHED THEN INSERT <br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  17. FrankKalis Moderator

    It has often been discussed if something like "UPSERT" make sense. I do think so. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />The only advantage I see currently when using<br /><br />WHEN MATCHED THEN UPDATE <br />WHEN NOT MATCHED THEN INSERT <br /><br />is, that you use a compiled, and hopefully highly optimized machine code to perform the operation, while the "traditional" approach uses interpreted T-SQL code. <br /><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>
  18. satya Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] funny enough MS developers do keep such funny names until they compile the components in SQL, one of my friend there mentioned the same.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  19. Adriaan New Member

    OK then - someone please run the MERGE syntax in the 2008 version, and report the trace.

Share This Page