x

Update Else Insert Procedure

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

asked Jun 16, 2010 at 03:31 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 voted first

This is a common problem often referred to as the UPSERT procedure. SQL Server finally addressed the issue in 2008 with the MERGE statement, but alas that doesn't help you here.

There are many solutions to this problem What you have done so far is a good start, the only change I would make is to just check for the existence of storenumber rather than the whole column list.

Another alternative is to introduce a control flag:

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_Nmae = @RVPName ,
  @updated = 1
WHERE
  store_number = @store

INSERT INTO stores
    (store,address,City,State,Zip,Phone,Latitude,
     Longitude,Chain,SM_Name,DM_Name,RVP_Name)
SELECT @store,@address,@City,@State,@Zip,@Phone,@Latitude,
       @Longitude,@Chain,@SMName,@DMName,@RVPName
WHERE
    @updated =0

this works well when the majority of UPSERTS are UPDATEs, rarely firing the last INSERT statement

more ▼

answered Jun 16, 2010 at 04:08 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

This is better(short) UPSERT, currently I'm doing an

UPDATE .. WHERE EXISTS(columns) INSERT .. WHERE NOT EXISTS(..)

Nice!
Nov 26, 2010 at 01:15 AM bonskijr
(comments are locked)
10|1200 characters needed characters left

Dave,

Today there was a question about inserting/updating table from DataTable. Please take a glance at my answer. Here is the link: http://ask.sqlservercentral.com/questions/7307/how-to-update-the-session-datatable-from-c-net-to-sqlserver-2008 If the solution is not acceptable for you (because you have SQL Sever 2005) then the least you can do with your stored procedure is change the contents of the exists. You have

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
)

which should be changed to

if exists(select 1 from zStore where store_number = @store)

This is because you simply need an evidence that the record exists or not in order to determine the course of action (update or insert), so there is no need to select anything to find that evidence.

If you would like to change the way it works altogether (switch from the row-level processing to a set-based proc), please let me know and I will append a complete example to this post showing how to achieve it with xml. If you have data in your front end which is represented by some sort of collection of records (DataSet, DataTable, or some custom type) and the total number of records to process at once is within thousands then xml solution might be suitable.

Oleg

more ▼

answered Jun 16, 2010 at 04:01 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Congrats on the 1k.
Jun 16, 2010 at 04:34 PM Grant Fritchey ♦♦
@Grant - Thank you.
Jun 16, 2010 at 05:00 PM Oleg
Indeed congratulations Oleg!
Jun 16, 2010 at 05:10 PM Kev Riley ♦♦
@Kev - Thank you. 4 digits (though low) are certainly better than 3 :)
Jun 16, 2010 at 06:21 PM Oleg
Congrats indeed! I am jealous... but I've not been going here for long.
Jun 16, 2010 at 06:47 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Nov 30, 2010 at 10:33 AM

dvroman gravatar image

dvroman
1.1k 2 2

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

x1945
x985
x408
x129
x104

asked: Jun 16, 2010 at 03:31 PM

Seen: 3756 times

Last Updated: Jun 16, 2010 at 03:56 PM