SQL Server Table Variables To Eliminate The Need For Cursors

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)

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: