UPSERT OR MERGE

Question

This is a followup to a question posed last week. The below SQL currently does insert data, but it is all NULLs. I have been looking at this too long and would like to have some of you out there put your eyes on it and see where my logic has gone awry. I know in 2008 there is a MERGE command, but I am not all that familiar with it's usage.

I would greatly appreciate your assistance.

BEGIN

declare @store int 
declare @Address varchar(50)
declare @City varchar(20)
declare @State char(255) 
declare @Zip varchar(10)
declare @Phone numeric (10,0)
declare @Latitude decimal(10,7) 
declare @Longitude decimal(10,7)
declare @Chain smallint
declare @SMName varchar(40)
declare @DMName varchar(50)
declare @RVPName varchar(50)
declare @status char
declare @updated bit
set @updated =0

      BEGIN
            IF EXISTS (Select store_number From zstore WITH (NOLOCK) 
            WHERE store_number = @store)
            UPDATE Stores 
            SET   store = @store,
                  Address = @Address,
                  city = @City,
                  State = @State,
                  Zip = @Zip, 
                  Phone = @Phone, 
                  Latitude = @Latitude, 
                  Longitude = @Longitude, 
                  Chain = @Chain, 
                  SM_Name = @SMName, 
                  DM_Name = @DMName,
                  RVP_Name = @RVPName,
                  status = @status,
                  @updated = 1
            WHERE store = @store

/* If the data is not found in the stores table then the row will be inserted */

             AND @status ='A' 
                  INSERT INTO stores (store,address,City,State,Zip,Phone,Latitude,Longitude,Chain,SM_Name,DM_Name,RVP_Name,status)
                  SELECT @store,@address,@City,@State,@Zip,@Phone,@Latitude,@Longitude,@Chain,@SMName,@DMName,@RVPName,@status
                  FROM zStore T2
                  LEFT OUTER JOIN stores T1 ON store = store_number
                          WHERE T1.store IS NULL -- If no match, insert this row

             IF   @status <>'A'  
                  INSERT INTO stores (store,address,City,State,Zip,Latitude,Longitude,Chain,SM_Name,DM_Name,RVP_Name,status)
                  SELECT @store,@address,@City,@State,@Zip,@Latitude,@Longitude,@Chain,@SMName,@DMName,@RVPName,@status
                  FROM zStore T2
                  LEFT OUTER JOIN stores T1 ON store = store_number
                          WHERE T1.store IS NULL -- If no match, insert this row
      END
END
GO

Answer 1

Don't use two statements in 2008. Use MERGE instead. Maybe this example will help you see how it works:

MERGE   INTO stores USING (SELECT @store) t(st)
ON      store = t.st
WHEN    MATCHED THEN UPDATE
SET     Address = @Address,
        city = @City,
        State = @State,
        Zip = @Zip, 
        Phone = @Phone, 
        Latitude = @Latitude, 
        Longitude = @Longitude, 
        Chain = @Chain, 
        SM_Name = @SMName, 
        DM_Name = @DMName,
        RVP_Name = @RVPName,
        status = @status,
        @updated = 1
WHEN    NOT MATCHED THEN
INSERT (store,address,City,State,Zip,Latitude,Longitude,
        Chain,SM_Name,DM_Name,RVP_Name,status)
VALUES (@store,@address,@City,@State,@Zip,@Latitude,@Longitude,
        @Chain,@SMName,@DMName,@RVPName,@status);

Answer 2

0

As it stands, you aren't setting any of the variables, so they will be NULL.

Then the code after AND @status ='A' will attempt the insert, as this AND is part of the update, but the indentation seems to suggest you are meaning something else...

answered Jul 07 '10 at 11:45 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

@Dave Myers In my answer to the previous question I mentioned that Kevan's suggestion to use the @updated flag is used precisely to avoid the conditionals, but you seem to revert it back to disregard it. Also, as Håkan pointed out in his answer, you use the zstore record which might not have the match in the stores table for your update. I think that the solution I gave you worked OK, but since I mentioned that the only reason I did not change the variables' usage was because I wanted to preserve the signature of the proc, I will add the answer to this question in few minutes which will include only one parameter that you ever need to make it work. I will do it later this evening. Please check this post in about 5 hours or so. I will try to comment it thoroughly.

Jul 07 '10 at 02:56 PM Oleg

@Dave Myers: After reading a very good answer by dportas I had to abandon the idea of submitting my answer :) The merge example given there should be all you need. As far as the inclusion of the @Phone is concerned, you can still use the logic in my answer to the previous question. The Phone column of the stores table is a nullable column, and since you have a business rule to include setting of the Phone should the value of the @status be equal to 'A', just set the value of the @phone variable to null if the @status is not equal to 'A'. This will automatically toggle the "inclusion" of the Phone setting, and you will not need to write a conditional insert logic.

if @status <> 'A' set @Phone = null;

should do the trick. It will allow you to remove one of the 2 insert statements you have. To be exact, use dportas answer and modify the following:

Add the line if @status <> 'A' set @Phone = null; before the merge statement

Add your variable setting statements

remove the comma after status = @status

remove the line @updated = 1

Include Phone, after Zip, in the insert part

Include @Phone, after @Zip, in the values part.

Jul 08 '10 at 07:57 AM Oleg

Oleg,

I appreciate your input and trust me I have not discounted anything you have provided. I respect you for taking the time and effort to provide assistance. Thank you

Jul 08 '10 at 09:16 AM Dave Myers

(comments are locked)

UPSERT OR MERGE

Follow this question

Related Questions