Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Apr 10, 2000

Use Temporary Tables Instead of Cursors in SQL Server

In Microsoft SQL Server, you frequently need to navigate through data row by row. This is usually done using T-SQL server-side cursors.

Considering that processing overhead on cursors is higher than on tables, I try to use a temporary table for such requirements. A table can be used for row-by-row operations, provided it has an unique identifier for each row. But this is not always true. Here is a technique to overcome that problem (example is based on a sales table of a pubs database):

 -- create the temporary table, adding a unique row identifier
SELECT CONVERT(varchar(40), NEWID()) 'rowid', *
INTO #temp
FROM sales

-- NEWID() is an T-SQL function that returns a unique value of type 
uniqueidentifier.
-- An unique identifier looks like: 6F9619FF-8B86-D011-B42D-00C04FC964FF

-- Now you can process one row at a time:

DECLARE @rowid varchar(40)
DECLARE @ord_date datetime

-- Get first row
SELECT @rowid = MIN(rowid)
FROM #temp

WHILE @rowid IS NOT NULL
BEGIN
   -- Do something with the row data
   SELECT @ord_date = ord_date
   FROM #temp
   WHERE rowid = @rowid

   PRINT 'Row id: ' + @rowid + '  order date: ' + convert(varchar(10), 
@ord_date, 111)

   -- Get next row id
   SELECT @rowid = MIN(rowid)
   FROM #temp
   WHERE rowid > @rowid
END

drop table #temp
Ravindra Okade
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date