automate row by row processing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

automate row by row processing

Author

Topic

satishk
Starting Member

39 Posts

Posted - 2007-09-05 : 01:43:09

Hi,

I am trying to automate the procedure wherein I want to achieve the below given code for all the sitecode column in table. I want to loop for all site code and need to pass year,month having (db1_Electricity_Usage-Gmers_Electricity_Usage)<>0 to another stored procedure which accepts year and month column

DECLARE sitecode_cursor CURSOR FOR
SELECT distinct sitecode from db1..site where len(sitecode)=8
ORDER BY userid
OPEN sitecode_cursor
FETCH NEXT FROM sitecode_cursor
INTO @sitecode
WHILE @@FETCH_STATUS = 0

insert into table1
SELECT table2.year,table2.month,left(table3.table3code,8),SUM(table2.electricityuse) 'ElecUse' FROM db1..table2 table2,db1..table3 table3 WHERE table3id
IN(SELECT idtable3 FROM db1..table3 WHERE table3code = @sitecode)
AND (year*100+month) BETWEEN 200511 AND 200706 AND idsvc=100 GROUP BY month, year,table3.table3code--, electricityuse
ORDER BY year, month
insert into table4
select year(rpt_dt) YEAR ,month(rpt_dt) MONTh, left (table3code,8) BU, SUM(KWh_Purchased_Quantity)'ElecUse'
from db2..table5
where table3code in
(select table3code from db1..table3 where table3code LIKE @sitecode) and len(table3code ) > 8 --)
and rpt_dt >= '2005-11-01' and rpt_dt < '2007-07-01' group by rpt_dt,left (table3code,8)
order by RPT_DT
select *,(db1_Electricity_Usage-Gmers_Electricity_Usage)as Diff from table1,table4
where table1.table3code=table4.gmmerstable3code and (db1_Electricity_Usage-Gmers_Electricity_Usage) <>0
and table1.year=table4.year and table1.month=table4.month.

Plz suggest how to achieve this or help me in writing this code soon.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 03:55:29

Not sure why you need a cursor, can't you just do a:


INSERT INTO table1
SELECT ...
FROM table2, table3
WHERE table3id IN
      (
          SELECT idtable3 
          FROM db1..table3 
               JOIN db1..site 
                   ON table3code = sitecode 
          WHERE len(sitecode)=8
      )

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:38:37

Kristen

quote:
Originally posted by satishk

I want to loop for all site code and need to pass year,month having (db1_Electricity_Usage-Gmers_Electricity_Usage)<>0 to another stored procedure which accepts year and month column

Seems that original table (with cursor) is some kind fo control table.
For each and one record, execute a SP with these values.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources