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




(@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 /


declare @updated bit set @updated =0

     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



more ▼

asked Jul 02, 2010 at 03:01 PM in Default

avatar image

Dave Myers
123 15 15 18

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

3 answers: sort voted first

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];
 set ansi_nulls on; 
 set quoted_identifier on; 
 -- 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)
 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]
     @store, @address, @City, @State, @Zip, 
     case when @status = 'A' then @Phone else null end, @Latitude,
     @Longitude, @Chain, @SMName, @DMName, @RVPName, @status
     where @updated = 0;

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.


more ▼

answered Jul 03, 2010 at 12:00 AM

avatar image

18.4k 3 7 28


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, 2010 at 06:59 AM Dave Myers
(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, 2010 at 10:46 PM

avatar image

Håkan Winther
16.6k 37 45 58

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

     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
     INSERT (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, 2010 at 05:54 AM

avatar image

10.8k 37 55 51

(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



Answers and Comments

SQL Server Central

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



asked: Jul 02, 2010 at 03:01 PM

Seen: 2079 times

Last Updated: Jul 02, 2010 at 03:01 PM

Copyright 2017 Redgate Software. Privacy Policy