In the past I've dealt a lot with cursors. My experience was mostly negative. SQL code written using them was terribly slow. Thus our team was transforming SQL code with cursors into code without them.
Sure, cursors have their advantages. But, after conversion SQL query performance can be much better then with cursors.
Now, I'll show you how to accomplish the task using SQL with and without cursors.
There are two tables: regions and values. For example, regions represents departments while values is used to store arbitrary int data about the specific region.
SQL tables:
create table regions (id int, name varchar(10))
create table [values] (id int identity(1, 1), regid int, value int)
Table regions contains records for 100 regions, while values contains data only for region with id = 1 (only for the first region). We need to populate values table using data from the first region.
For exaple if table regions contains data
| id | name |
| 1 | Reg 1 |
| 2 | Reg 2 |
And table values contains data only for first region
| id | Reg Id | value |
| 1 | 1 | 30 |
| 2 | 1 | 35 |
So we need to populate table values for every region from regions table.
There are three ways how to do this:
- write a computer program that will insert values into values table
- write SQL query with cursors
- write SQL query without cursors
But last two options seem to be interesting. Cursor solution is quick and dirty - you can code it almost in no time. Cursor code will be similar to the computer program's one.
select id,regionid,value into #Valuestmp from [values]
DECLARE RegionsCursor CURSOR FOR SELECT id FROM regions
declare @id int
OPEN RegionsCursor;
FETCH NEXT FROM RegionsCursor into @id;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Valuestmp SET RegionId=@id
INSERT INTO [values](regionId, value) SELECT regionId, value FROM #Valuestmp
FETCH NEXT FROM RegionsCursor into @id;
END;
CLOSE RegionsCursor;
DEALLOCATE RegionsCursor;
drop table #Valuestmp
Here we store initial values from values table, and then for each region we insert data into values table.
This elegant script will accomplish the above:
insert into val (regid, value) select r.id as regid, v.value as value from reg r, val v where r.id <> 1
Central point in this script is Cartesian product - we select from two tables (bold font), joining every row of the values table with the rows in regions table.
SQL code above shows how cursor can be avoided. Every time you're tempted to use cursor - stop and think that nearly everything can be done without them. All you have to do is to think little bit.
Posts
