x

UPSERT OR MERGE

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
more ▼

asked Jul 07, 2010 at 11:39 AM in Default

avatar image

Dave Myers
123 15 15 18

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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);
more ▼

answered Jul 07, 2010 at 03:25 PM

avatar image

David 1
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jul 07, 2010 at 11:45 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

@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, 2010 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, 2010 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, 2010 at 09:16 AM Dave Myers
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x164
x138
x53

asked: Jul 07, 2010 at 11:39 AM

Seen: 2070 times

Last Updated: Jul 07, 2010 at 11:45 AM

Copyright 2016 Redgate Software. Privacy Policy