Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] Re: People say MERGE can UPSERT but not clear to me

Expand Messages
  • canlan@yahoo.com
    Ok Connected by DROID on Verizon Wireless ... From: Damhuis Anton To: SQLQueriesNoCode@yahoogroups.com
    Message 1 of 3 , Oct 12, 2011
    View Source
    • 0 Attachment
      Ok
      Connected by DROID on Verizon Wireless

      -----Original message-----
      From: Damhuis Anton <DamhuisA@...>
      To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
      Sent: Thu, Oct 13, 2011 05:14:40 GMT+00:00
      Subject: [SQLQueriesNoCode] Re: People say MERGE can UPSERT but not clear to
      me

      Hmm this is an interesting command. I have not come across it before.

      I had a look at this link:
      http://technet.microsoft.com/en-us/library/bb510625.aspx
      and came up with this small sample below.
      Basically you take your parameters and "put" them into a table in the Using
      clause.

      --Begin SQL code
      IF OBJECT_ID('Users') IS NOT NULL DROP table Users;
      IF OBJECT_ID('MergeUsers') IS NOT NULL DROP PROC MergeUsers;

      Create table Users (ID int, Name varchar(50));

      Insert into Users values (1, 'Anton');
      Insert into Users values (2, 'Andre');
      go

      create proc MergeUsers (@p_ID int, @p_Name varchar(50))
      as
      Begin
      Merge into Users as Target
      Using (Select @p_ID , @p_Name) as Source (Id, Name)
      On (Target.Id = Source.ID)
      When Matched then
      Update Set Name = @p_Name
      When not Matched then
      Insert (ID, Name)
      Values (source.ID, Source.Name);

      End
      go

      Select * from Users

      exec MergeUsers 1, 'Rudolf';
      exec MergeUsers 3, 'Paul'

      Select * from Users

      Drop table Users
      Drop proc MergeUsers
      --End SQL code


      Regards
      Anton Damhuis

      [cid:image001.jpg@...5895E0]

      Building 1, Inanda Greens Office Park, 54 Wierda Road West
      Wierda Valley, PO Box 786055, Sandton 2146
      e-mail: DamhuisA@...<mailto:DamhuisA@...>
      switchboard: +27 (11) 505 6000
      telephone: +27 (11) 505 6326

      www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
      Investment Solutions Limited is a licensed financial service provi



      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.