Creating a conditional UPDATE and/or INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a conditional UPDATE and/or INSERT

Author

Topic

pwvailla
Starting Member

31 Posts

Posted - 2010-11-17 : 12:42:32

Hi, I have been useing SQL Server 2005 for a couple of months and am looking to create a conditional UPDATE and/or INSERT statement such that if the source data key fields exist in the destination table, do an UPDATE, if not, do an INSERT. It seems simple enough, but I am struggling. Yes, I hear there is a MERGE command in v2008, but I am stuck with v2005. Also, I should mention my "key" is made up byt two fields.

I envision an IF.... ELSE type of flow control but is it possible? If so, how?

Rough example:

I pull data from an Oracle ERP Purchasing (OPS) table
1. PO char8
2. PO_lINE number 8
3. DESC char50
4. DELIVERY_DATE datetime

Every day this gets refreshed in a destination table: PO_STATUS

Would something like this work?

IF po, poline exists in(
select po, poline from po_status
where OPS.PO = PO_STATUS.PO and OPS.PO_LINE = PO_STATUS)
THEN
UPDATE PO_STATUS
SET
PO_STATUS.DESC = OPS.DESC
PO_STATUS.DELIVERY_DATE = OPS.DELIVERY_DATE
FROM OPS
WHERE
PO_STATUS.PO = OPS.PO and PO_STATUS.PO_LINE = OPS.PO_LINE
ELSE
INSERT INTO PO_STATUS (PO, PO_LINE, DESC, DELIVERY_DATE)
FROM OPS
END

I will be inserting/updating status on 100s of rows each day.

Is this update/insert kind of coding possible? You don't need to use my fields/tables in your example. In real life I have to join 2-3 tables as part of this update / insert process. Can this be done within a stored procedure too?

Thanks in advance

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-17 : 12:52:57

What about the delete?


	UPDATE P
	   SET O_STATUS.DESC = OPS.DESC
		 , PO_STATUS.DELIVERY_DATE = OPS.DELIVERY_DATE
	  FROM OPS
	  JOIN PO_STATUS P
	 WHERE P.PO = OPS.PO and P.PO_LINE = OPS.PO_LINE

	INSERT INTO PO_STATUS (PO, PO_LINE, DESC, DELIVERY_DATE)
		   FROM OPS o1
          WHERE NOT EXISTS (SELECT * FROM po_status p 
						     WHERE o1.PO = p.PO and O1.PO_LINE = p.PO_STATUS)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-18 : 06:12:53

quote:
Originally posted by X002548

What about the delete?

UPDATE P
SET O_STATUS.DESC = OPS.DESC
, PO_STATUS.DELIVERY_DATE = OPS.DELIVERY_DATE
FROM OPS
JOIN PO_STATUS P
ON P.PO = OPS.PO and P.PO_LINE = OPS.PO_LINE

INSERT INTO PO_STATUS (PO, PO_LINE, DESC, DELIVERY_DATE)
FROM OPS o1
WHERE NOT EXISTS (SELECT * FROM po_status p
WHERE o1.PO = p.PO and O1.PO_LINE = p.PO_STATUS)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

You need ON onstead of WHERE

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 09:34:21

D'oh

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources