question

Dave Myers avatar image
Dave Myers asked

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
insertmerge
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.