How Can I Keep Tblpurchase And Tblproductstock Table Without Drop. (i Need Keep Both Table And Value Permanent Without Drop)

How to change this stored procedure without drop tblPurchase and tblProductStock. When I run my program with this stored procedure after adding new record table and data will be dr

Solution 1:

Indexed View

---

An entirely new solution based on Indexed Views is possible.

An Indexed View is a view which has a clustered index on it, and the data is actually stored on disk.

As I understand it, you are trying to keep a sum of purchases per product item stored in tblProduct. I have assumed that ItemCode is the PK of tblProduct and that ItemName is also defined there (We cannot use MAX in an indexed view). So we can define a view like this:

CREATEVIEW dbo.vwTotalPurchases
WITH SCHEMABINDING  -- must be schema bound, we cannot change underlying columns after creationASSELECT
   ItemCode,
   SUM(Quantity) QuantityPurchased,
   COUNT_BIG(*) CountPurchases  -- if we group, must have count also, so that rows can be maintainedFROM dbo.tblPurchase  -- must use two-part namesGROUPBY itemCode;
GO

We can then create a clustered index on it to persist it on disk. SQL Server will maintain the index whenever an update to the base table happens. If there are no more rows in the grouping (identified by count being 0), then the row is deleted:

CREATEUNIQUE CLUSTERED INDEX PK_vwTotalPurchases ON dbo.vwTotalPurchases (ItemCode);
GO

Now if we want to query it, we can left join this view onto tblProducts (left join because there may be no purchases):

SELECT
    p.ItemCode,
    p.ItemName,
    ISNULL(tp.QuantityPurchased, 0) QuantityPurchased,
    ISNULL(tp.CountPurchases, 0) CountPurchases
FROM tblProducts p
LEFTJOIN vwTotalPurchases tp WITH (NOEXPAND) ON tp.ItemCode = p.ItemCode;

We can define this as a view also (not an indexed one, but a standard view) so that the definition is usable anywhere.

---

Note on NOEXPAND:

If you are not on SQL Server Enterprise or Developer Edition, you must use the hint WITH (NOEXPAND) to force it to use the index, otherwise it will query the base tblPurchase instead. And even in those editions, it is best to use NOEXPAND.

See this article by Paul White on this.