Question about inserting data - 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

General SQL Server Forums

New to SQL Server Programming

Question about inserting data

Author

Topic

FrumpleOrz
Starting Member

2 Posts

Posted - 2011-06-26 : 16:12:19

Hi there, sorry if this has been asked before. I'm pretty new to the SQL stuff.

I've got a table of stock levels and whenever I get new inventory I want to be able to add it to the current table. I was wondering if there was a way to have so if an item already exists in the table, it will just add to the available quantity but if it can't find the record, it will append the table with the new item.

For example, I have a table like this:

ItemNumber Quantity
Apples 3
Oranges 2
Pears 0

and the new inventory I have looks like this:

ItemNumber Quantity
Apples 1
Peaches 2
Pears 1

and I want the end result to look like this:

ItemNumber Quantity
Apples 4
Oranges 2
Pears 1
Peaches 2

Is there a simple way to get these results? Thanks in advance.

FrumpleOrz
Starting Member

2 Posts

Posted - 2011-06-26 : 20:05:47

Okay, I ended up using the following piece of code. Can anyone see any sort of problem the following could cause?

insert into Inventory (ItemNumber, Quantity)
Select
ItemnumberTest,NewItems.quantity
from
Inventory
right join newitems on inventory.ItemNumber = newitems.itemnumbertest
where
Inventory.ItemNumber is null
go

update Inventory
set Inventory.quantity = (Inventory.Quantity + newitems.quantity)
from
Inventory
left join newitems on inventory.ItemNumber = newitems.itemnumbertest
where NewItems.ItemNumberTest is not null
go
delete from NewItems

edit: Ha. I see an issue already. I'm adding the new items after I've added the rows, so it'll double the quantity of the new item. Okay, so if I switch the order of the queries there, can anyone see another issue?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-26 : 21:44:14

I would do the update first, and then do the inserts (and then delete everything from newitems table)

update Inventory set 
   Inventory.quantity = (Inventory.Quantity + newitems.quantity)
from
   Inventory
   inner join newitems on inventory.ItemNumber = newitems.itemnumbertest
where 
   NewItems.ItemNumberTest is not null; -- the where clause may not be required.

insert into 
   Inventory
select 
   ItemnumberTest,NewItems.quantity
from
   newitems
where
   not exists
   (
      select * from Inventory
      where Inventory.ItemNumber = newitems.ItemnumberTest
   );

truncate table newitmes;

You may want to wrap all the statements in a transaction and a try/catch block (if you are on SQL 2005 or higher).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources