Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Server insert / update

In a previous question I received the attached code for an insert.

What I just discovered is that if the offerid exists I need to run an update first...else run the insert statement.
insert into dbo.tblSpecials
      (
      offerID,
      resort,
        blah...blah...blah...
      )
      select      
            a.offerID,
            a.resort,
            a.address1,
            blah...blah...blah....
      From      dbo.tblSpecialsImport a with(nolock) LEFT JOIN dbo.tblSpecials b ON a.offerID=b.offerID
      WHERE b.offerID IS NULL

Open in new window

Avatar of jachustz
jachustz
Flag of United States of America image

You could do a select projectid from tblspecials where fldofferid = @offerid and then check if anything was returned.  If so...that means a record with that offerid already exist and you need to do an update.  If not....do an insert

If the the result is null then
insert blah blah
else
update tblspecials set blah = blah
where fldofferid = @offerid

I can give you better code if you need....this is a possibilty of how the logic could be structured.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The simplest way to do that is run the update first and right after that check the@@rowcount, which is how many rows were affected by last statement. If 0 do the insert:
UPDATE 
	bblah, blah,blah
      WHERE b.offerID IS NULL
-- right after that
IF @@rowcount=0
	insert into dbo.tblSpecials
		  (
		  offerID,
		  resort,
			blah...blah...blah...
		  )
		  select      
				a.offerID,
				a.resort,
				a.address1,
				blah...blah...blah....
		  From      dbo.tblSpecialsImport a with(nolock) LEFT JOIN dbo.tblSpecials b ON a.offerID=b.offerID
		  WHERE b.offerID IS NULL

Open in new window

That may not necessarily work.....there may be records that get updated...but there also may be records that need to be inserted.... you know what I mean?  If it is one record at a time, then it works fine.  But if there are more it may not.
Avatar of Larry Brister

ASKER

chapmandew ,

Understood
Doing an exist check may be more helpful
CREATE PROCEDURE [dbo].[OrderEdit]
 
(
@OrderID int,
@Description varchar(50)
)
 
 AS
 
if exist select * from tblOrder where fldOrderID = @OrderID
 
update tblOrder set fldOrderID = @OrderID, fldOrderDesc = @Description
where fldorderID = @orderID
 
else
 
Insert into tblOrder (fldOrderID, fldOrderDesc) values (@OrderID, @Description)

Open in new window