UPDATE and INSERT IF NOT EXIST problems

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

SQL Server 2005 Forums

Transact-SQL (2005)

UPDATE and INSERT IF NOT EXIST problems

Author

Topic

firegeek
Starting Member

5 Posts

Posted - 2011-05-06 : 11:00:51

Hey Everybody,
I am trying to execute an UPDATE/INSERT IF NOT EXISTS statement. The statement executes without errors however I only get an update on existing records and the insert is not occuring.

I have tried two methods with identical results.

METHOD 1:

UPDATE tblInItem
	SET tblInItem.Descr = u.Descr,
		tblInItem.ItemType = u.ItemType,
		tblInItem.ItemStatus = u.ItemStatus
	FROM tblInItemUpdate u
	WHERE tblInItem.ItemId = u.ItemId
IF @@ROWCOUNT=0
    INSERT INTO  tblInItem (ItemId, Descr, ItemType, ItemStatus, Uom)
	SELECT u.ItemId, u.Descr, u.ItemType, u.ItemStatus, u.Uom
	FROM tblInItemUpdate u

METHOD 2:

IF EXISTS (SELECT * FROM tblInItem, tblInItemUpdate WHERE tblInItem.ItemId = tblInItemUpdate.ItemId)
    UPDATE tblInItem
	SET tblInItem.Descr = u.Descr,
		tblInItem.ItemType = u.ItemType,
		tblInItem.ItemStatus = u.ItemStatus
	FROM tblInItemUpdate u
	WHERE tblInItem.ItemId = u.ItemId
ELSE
    INSERT INTO  tblInItem (ItemId, Descr, ItemType, ItemStatus, Uom)
	SELECT u.ItemId, u.Descr, u.ItemType, u.ItemStatus, u.Uom
	FROM tblInItemUpdate u

I don't have a preference between methods, I simply want one that executes as desired. I'm wondering if I must use some sort of JOIN however I'm not very familiar with joins.

btw, I'm a very beginner SQL user so please pardon any ignorance on my part. ;)

Thank you in advance for help!
-jeremiah

MSquared
Yak Posting Veteran

52 Posts

Posted - 2011-05-06 : 11:18:16

How many records are in the tblInItemUpdate table when this is run. The insert is inserting the entire table regardless if the ItemID is in the tblInItem table or not. Can you provide some sample data?

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

firegeek
Starting Member

5 Posts

Posted - 2011-05-06 : 11:46:22

Thanks for the posting guidelines...

Scenario:
I would like to update an active inventory table (tblInItem) using data from another source table (tblInItemUpdate) where 'ItemId' is the primary key in tblInitem. tblInItem contains roughly 1500 records and tblInItemUpdate contains 54 records (34 of which are new/do not exist). I would like to update the 20 existing records and insert the other 34 records. (fwiw, tblInItemUpdate contains 18 columns...used to update various existing tables)

Sample data:
tblInItem
ItemId, Descr, ItemType, ItemStatus, Uom
ALC-PEL-24, Ladder, 1, 1, EA
PEL-2000, Flashlight A, 1, 1, EA
PEL-2400, Flashlight B, 1, 1, EA
KID-897880, Water Can, 1, 1, EA

tblInItemUpdate
ItemId, Descr, ItemType, ItemStatus, Uom
PEL-2000, Flashlight A (yellow), 1, 1, EA
PEL-2400, Flashlight B (yellow), 1, 1, EA
PEL-770, Universal Light Holder, 1, 1, EA
KID-897880, Water Can, 1, 2, EA

desired result set:
ItemId, Descr, ItemType, ItemStatus, Uom
ALC-PEL-24, Ladder, 1, 1, EA
PEL-2000, Flashlight A (yellow), 1, 1, EA
PEL-2400, Flashlight B (yellow), 1, 1, EA
PEL-770, Universal Light Holder, 1, 1, EA
KID-897880, Water Can, 1, 2, EA

When I execute the code shown in my previous post, the result is an update of the 20 matching records in tblInItem and the 34 new records from tblInItemUpdate are not inserted into tblInItem.

I hope this helps clarify my situation.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-06 : 13:22:28

The "issue" is that the update runs and succeeds everytime so the INSERT never runs. Try using:
1. ~~The MERGE statement~~ EDIT: Forgot this was 2005
2. or do the Insert before the Update.

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-05-10 : 02:49:06

try to use merge statement . u can use merge for update ,insert, delete..

MSquared
Yak Posting Veteran

52 Posts

Posted - 2011-05-10 : 08:08:37

Merge is a function in SQL 2008, so you can't use that. The If statement is what is throwing you off and you don't need it. Normally, I would update the existing records first, then insert any new ones. Try this

-- Update existing records
UPDATE tblInItem
SET tblInItem.Descr = u.Descr,
tblInItem.ItemType = u.ItemType,
tblInItem.ItemStatus = u.ItemStatus
FROM tblInItemUpdate u
inner join tblInItem
WHERE tblInItem.ItemId = u.ItemId

-- Insert new records
INSERT INTO tblInItem (ItemId, Descr, ItemType, ItemStatus, Uom)
SELECT u.ItemId, u.Descr, u.ItemType, u.ItemStatus, u.Uom
FROM tblInItemUpdate u
left outer join tblInItem
on tblInItem.ItemId = u.ItemId
where tblInItem.ItemId is null

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources