conditional insert/append - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

conditional insert/append

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-30 : 15:33:28

Can anyone tell me how to insert a new row if a field doesn't match a variable and if it does match the varibale exist then all I want is to adjust the row.

ie:

'TABLENAME'
Stock , Price
A , 100
B , 200
C , 300

If the stock I am inserting into the table doesn't exist, we'll call it 'D', then I want to add a new row with D and its' associated price. If the stock does exist, say 'B', then I want to affect the already existing row.

I was thinking something like this:

IF @Stock != Stock
THEN
INSERT INTO 'TABLENAME' (Stock, Price)
SELECT @Stock, @Price
ELSE
UPDATE 'TABLENAME'
SET Price = @Price
WHERE @Stock = Stock

The statement isn't working but it should give you a basic idea of what I want. Does anyone have any suggestions?

Cheers,
dirwin

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-30 : 17:38:46

You need to run Inserts and Updates as separate statements. Run the Update first. This method will work for inserting single records. You will need to handle the Insert differently if you are dealing with multiple records.

Update	TableName
set	Price = @Price
where	Stock = @Stock

if @@RowCount = 0
Insert into TableName
	(Stock,
	Price)
values	(@Stock,
	@Price)

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2005-10-30 : 18:44:25

You can use "exists". Please refer to this article;

http://www.sqlteam.com/item.asp?ItemID=14820

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-10-30 : 20:48:15

Implementation using exists as suggested by saglamtimur

-- This will insert the record if not exists
insert into TableName (Stock, Price)
select @Stock, @Price
where not exists (select * from TableName x
where x.Stock = @Stock)

-- This will update the record
update TableName
set Price = @Price
where Stock = @Stock
and Price <> @Price

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-10-31 : 09:12:40

they all worked great. thanks for the help!

Dirwin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources