asked
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
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);
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...
@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.
@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.
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
