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 */
declare @updated bit set @updated =0
/* If the data is not found in the stores table then the row will be inserted */
asked Jul 02 '10 at 03:01 PM in Default
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
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:
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.
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.
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)
Try it will work
answered Jul 08 '10 at 05:54 AM