Write Query Results back to Table

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)

Write Query Results back to Table

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 11:13:21

Does anyone know if it is possible to write data back to a table based on the results of a query?

I have an inventory management database that logs transactions whether they be sales or receipts, etc. The query I have calculates current onhand from all transactions in the table for each item. I've pasted the SQL for my query below.

What I'd like to do is write this data back to the main product table and have it update very regularly. I need to have this "Current Onhand" picture regularly to do inventory evaluations and purchasing based on CURRENT levels.

Anyone have any good ideas? I'm having a hard time integrating the query results DIRECTLY onto the forms I'm using possibly in part because I'm using MS Access as my front end UI.

SELECT Transactions.Item, Sum([Transactions.QTY]*IIf([Type]='Credit',1,-1)) AS CurrentOnhand
FROM Transactions
GROUP BY Transactions.Item;

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 11:21:31

Use a trigger to achieve this OR use a Sql job to run on a schedule.

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-14 : 11:27:30

In terms of the actual code (which I think is what you were getting at), there's no reason why you couldn't post those results back into another table with a SELECT INTO or an UPDATE bit of code.

Something like. .

SELECT Transactions.Item, Sum([Transactions.QTY]*IIf([Type]='Credit',1,-1)) AS CurrentOnhand
INTO NewTable
FROM Transactions
GROUP BY Transactions.Item

Then you can report from NewTable.

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 13:18:22

Thank you both for your posts on this. I'm a little fuzzy on specifics since I'm a little new to SQL. Can I create a trigger to update the columns I need to update (I already have them in place) using code similar to what you've suggested above?

Thanks for your help!!

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 13:31:31

You can create a trigger on your inventory table so that any time a item is removed or added, the count gets updated.

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 16:24:30

So, the code above....is it something I'd put into a stored procedure or a function, or part of the trigger?? I get the concept, but I don't know where I'm supposed to be coding. I guess it shows that I'm new.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-15 : 00:44:01

Hi,
better way is to create a view joining product and transaction table which will always reflect actual inventory.
Using a trigger for this purpose ia an overhead and may affect the performance
Kunal

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-15 : 10:16:35

Thanks kunal. That could be very helpful. Can you give me a little more detail on creating the view? I'm the reluctant DBA who is kind of starting on SQL, so that's a little fuzzy for me. Also, I'm not sure that I'm overly concerned about overhead affecting performance. This DB should be pretty small in scope so I don't think we'll run up against any performance ceilings any time soon. At least I hope.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-15 : 23:43:57

Hi,

take this example

tblProduct
(productId, name)
tblPurchase
(PurchaseId,date,productId, quantity)
This will add up to stock

tblSale
(SaleId,date,productId, quantity)
This will be subtracted from stock

tblInitialStock
(stockId,productId,quantity)
This will add up to stock

Create view inventory as

select p.productid,pu.qty from tblProduct p
inner join tblPurchase pu on pu.productid=p.productid
union
select p.productid,s.qty from tblProduct p
inner join tblstock s on s.productid=p.productid
union
select p.productid,(sa.qty*-1) qty from tblProduct p
inner join tblsales sa on sa.productid=p.productid

this will give u a view showing inventory of each product. u can query this view to get current stock of each item

select productid, sum(qty) from inventory
group by productid

Kunal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources