TSQL -> Update calculated value using TEMP table

The mission is to calculate quantity of items sold in 1999 and add the resulting number into Items table. In order to execute the task, we first add column SaleQnt1999 to the Items table, than create TEMP table #temp_salesqnt1999, than run Query that calculates quantities and put it's results into the TEMP table. At the end we update SaleQnt1999 column using data from TEMP table.

/* create new column in Items table */
ALTER TABLE dbo.items ADD SaleQnt1999 INT;

/* create TEMP table */
CREATE TABLE #temp_salesqnt1999
  (itemcode nvarchar(25) NOT NULL,
   totqnt int NULL);

/* calcualate sales quantity for year 1999 */
INSERT INTO #temp_salesqnt1999 (itemcode, totqnt)
SELECT a.itemcode, SUM(a.qnt) AS totqnt
FROM invlines AS a INNER JOIN invoice AS b
ON a.dockey = b.dockey
WHERE Year(b.docdate)=1999
GROUP BY a.itemcode
ORDER BY a.itemcode;

/* create index on TEMP table to boost performance */
CREATE UNIQUE CLUSTERED INDEX indx_1 ON #temp_salesqnt1999 (itemcode);

/* update column SaleQnt1999 using calculated quantity from TEMP table */
UPDATE a SET a.SaleQnt1999 = b.totqnt FROM dbo.items AS a INNER JOIN #temp_salesqnt1999 AS b ON a.itemcode = b.itemcode;

/* drop TEMP table */
DROP TABLE #temp_salesqnt1999;

SELECT itemcode, SaleQnt1999 FROM dbo.items ORDER BY itemcode;


The result will be like this:
itemcodeSaleQnt1999
0036876116
5004097NULL
5003502120
......




sqlexamples.info