creating Temp Varaible.

http://www.eggheadcafe.com/articles/20010823.asp



Let's take a very simple example that you can test for yourself. We'll use the trusty old Northwind sample database. Let's say that you run the Northwind Traders operation and you suddenly find a new shipper whose prices on everything that you normally ship with shipper number "1" that costs over a certain price level ( let's say we've determined the level is $50.25) can now be shipped with your new shipper number 4 for a flat rate of $21. You think, "Oboy - I'll be able to put in for a raise on this one!'

So you want to update all your open orders that had instructions to use Shipper 1 where the price came out to be greater than $50.25, and change the shipper to shipper number 4 and the new flat rate of $21.00.

Now before we begin, I know and you know that we can definitely do this with a single SQL Statement using a correlated subquery with a derived table holding the items to change. But for the sake of simplicity, let's just say for the sake of example that we've decided we need to do it either with a cursor or a temporary table. Now let's see how we could do it using the new TABLE variable, and avoid all the extra disk access and locking:

USE NORTHWIND

-- we declare our table variable first

declare @SpecialCustomers TABLE (
CustomerID nchar (5) NOT NULL ,
OrderID int NOT NULL ,
ShipVia int NOT NULL,
Freight money NOT NULL)

-- now we populate the in-memory table variable with the record information needed for the update

insert into @SPecialCustomers select CustomerID, OrderID, ShipVia, Freight
from dbo.Orders where ShipVia =1 AND Freight >50.25

-- and finally we update the affected records in our regular orders table with our new shipper and price information,
-- using the @SpecialCustomers TABLE variable just as we would a real, physical table in the database:

UPDATE ORDERS SET ShipVia=4, Freight =21.00
where ORDERS.OrderID IN (SELECT ORDERID FROM @SpecialCustomers)