The procedure will check to see if new store information has been added to the wwwStore.stores table. If it is new it will insert the new values into the table. If the store information exists, but is being modified (e.g. new manager) then it will update the row.
I know there is probably a better way to do this, as I am sure this will have to scan to perform the operation, but I would appreciate it if you could comment and/or provide suggestions.
I appreciate your time and support
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),
AS
If Exists (Select
store_number,street_address AS address,city,state,
zip_code + zip_ext AS zip,Phone_Number AS Phone,
Latitude,Longitude,Chain,SM_Name,DM_Name,RVP_Name
From zStore WHERE store_number = @store)
UPDATE stores
SET Address = @Address
SET City = @City
SET State = @State
SET Zip = @Zip
SET Phone = @Phone
SET Latitude = @Latitude
SET Longitude = @Longitude
SET Chain = @Chain
SET SM_Name = @SMName
SET DM_Name = @DMName
SET RVP_Nmae = @RVPName
WHERE store = @store
ELSE
INSERT INTO stores
(store,address,City,State,Zip,Phone,Latitude,
Longitude,Chain,SM_Name,DM_Name,RVP_Name)
VALUES (@store,@address,@City,@State,@Zip,@Phone,@Latitude,
@Longitude,@Chain,@SMName,@DMName,@RVPName)