x

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

I am needing to compare the store_number from two different tables in the same database. If a store_number in Table 1 is not in Table 2 then insert a row. Likewise if the status column in table 1 is = 'A' then it will include the phone_number field in that row data.

I would like this to be able to be run as a stored proc and/or from an SSIS package.

What I have done here is not updating the 2nd table, it completes successfully but nothing gets updated - I know the first portion is designed for input, I am more concerned with the logic of the comparison and insertion.

Your assistance would be greatly appreciated.


USE [wwwStores] GO

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

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)

/* pull data from the zStore table and see if it is already in the stores table, if it is found in the table, it will update the row with any information that has changed */

AS BEGIN

declare @updated bit set @updated =0

BEGIN
    IF EXISTS (Select store_number From zStore WITH (NOLOCK) 
    WHERE store_number = @store)
    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
    WHERE store = @store

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

    ELSE IF @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
       WHERE  @updated =0

    ELSE 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
       WHERE  @updated =0
    END

END

GO
more ▼

asked Jul 02 '10 at 03:01 PM in Default

Dave Myers gravatar image

Dave Myers
123 15 15 16

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

3 answers: sort newest

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

more ▼

answered Jul 03 '10 at 12:00 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

Oleg,

Appreciate the assistance. It helps to have another set of eyes to look at the SQL, sometimes you convince yourself that you have done everything you need. FYI changed the phone# to numeric(10,0)
Jul 06 '10 at 06:59 AM Dave Myers
(comments are locked)
10|1200 characters needed characters left

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

answered Jul 08 '10 at 05:54 AM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jul 02 '10 at 10:46 PM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x103
x47

asked: Jul 02 '10 at 03:01 PM

Seen: 1636 times

Last Updated: Jul 02 '10 at 03:01 PM