Compare store numbers in 2 tables and insert the rows from table 1 that do not have the same store numbers in table 2

As Håkan already pointed out in his answer, your problem originates from the wrong table in the exists. I would like to elaborate on it a little bit. From your question, I see that you gather data from the table named zStore and pass all columns' values as parameters to the proc. In reality, there is no need to do this at all as you can simply

• update from the join of the 2 tables based on the store_number and set the @updated flag should the update affect existing record

• insert into stores select (your_columns) from zStore where store_number = @store and @updated = 1.

The @updated flag comes from Kevan's excellent suggestion in one of the earlier questions, and its purpose is to actually get rid of a conditional statements (if -> else if...), but in your logic, it happens to loose its purpose.

Since it is possible that you need to keep the signature of your procedure as is, I will still leave this part unchanged. Here is the script:

use [wwwstores];
go

set ansi_nulls on; 
go
set quoted_identifier on; 
go

-- please check the data type of the @Phone variable. It is declared as int, but 
-- the phone numbers are typically 10 digits long. If this is the case and the 
-- first digit of the @Phone is greater than 2, you might get overflow errors 
-- when the proc is called to execute.
create procedure [dbo].[usp_store_sync] 
(
    @store int, @Address varchar(50), @City varchar(20), 
    @State char(255), @Zip varchar(10), @Phone int, 
    @Latitude decimal(10,7), @Longitude decimal(10,7), 
    @Chain smallint, @SMName varchar(40), @DMName varchar(50), 
    @RVPName varchar(50), @status char(1)
)
as

begin

declare @updated bit;

set @updated = 0;

update Stores 
    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
    from zStores inner join Stores
       on zStores.store_number = Stores.store
    where zStores.store_number = @store;

-- If the record did not exist in the stores table then the proc will insert it 
-- (regardless of the value of status). From the original logic it is easy to 
-- deduce that Phone column is nullable, and therefore, instead of placing the 
-- logic in the else if -> else if blocks, it could be cheap enough to simply 
-- set the value of the @phone variable to null should the @status variable be 
-- not equal to 'A'. This will automatically toggle "the inclusion" of the 
-- Phone value for insert on as needed basis.
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, 
    case when @status = 'A' then @Phone else null end, @Latitude,
    @Longitude, @Chain, @SMName, @DMName, @RVPName, @status
    where @updated = 0;

end;
go

Please note that I added the brackets to those column names which happen to be T-SQL keywords, such as address, state and status, and also added the size to the @status declaration.

Oleg

Thats ryt Håkan Winther

The new MERGE statement is a standard statement IN SQL SERVER 2008 that combines INSERT, UPDATE, and DELETE actions as a single atomic operation based on conditional logic. Besides being performed as an atomic operation, the MERGE statement is more efficient than applying those actions individually.

MERGE Stores as TARGER USING (SELECT @store,@Address,@City,@State,@Zip,@Phone,@Latitude,@Longitude,@Chain,@SMName,@DMName,@RVPName,@updated,@Status) AS SOURCE( store_number,Address,city,State,Zip,Phone,Latitude,Longitude,Chain,SM_Name,DM_Name,RVP_Name,updated,Status) ON (TARGER.store_number = Source.store_number)

WHEN MATCHED THEN 
    UPDATE  SET Address = Source.Address,
         city = Source.City,
         State = Source.State,
         Zip = Source.Zip, 
         Phone = Source.Phone, 
         Latitude = Source.Latitude, 
         Longitude = Source.Longitude, 
         Chain = Source.Chain, 
         SM_Name = Source.SM_Name, 
         DM_Name = Source.DM_Name,
         RVP_Name = Source.RVP_Name,
         status = Source.status,
         updated = Source.updated

    WHEN NOT MATCHED THEN

    INSERT (store_number,Address,city,State,Zip,Phone,Latitude,Longitude,Chain,SM_Name,DM_Name,RVP_Name,updated,Status)
    VALUES(store_number,Address,city,State,Zip,Phone,Latitude,Longitude,Chain,SM_Name,DM_Name,RVP_Name,updated,Status);

Try it will work

In your IF EXISTS statement you are searching zStore to see IF it does exists but it is no guarantee that the record exists in store table (the one you want to update). Try to change the table to store . Another thing you may want to try IF you have sql2008 is the merge statement.