cursor alternate - SQL Server Forums

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)

cursor alternate

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-07-18 : 20:03:44

Hi,

I have following record set

cust product month
ABC 1234 Jul-07
ABC 1234 Sep-07
XYZ 567 Sep-07
XYZ 567 Oct-07
ABC 567 Aug-07
PQRS 897 Nov-07

which has to be inserted with records

cust product month
ABC 1234 Jul-07
ABC 1234 Aug-07
ABC 1234 Sep-07
ABC 1234 Oct-07
ABC 1234 Nov-07
ABC 1234 Dec-07
XYZ 567 Jul-07
XYZ 567 Aug-07
XYZ 567 Sep-07
XYZ 567 Oct-07
XYZ 567 Nov-07
XYZ 567 Dec-07
ABC 567 Jul-07
ABC 567 Aug-07
ABC 567 Sep-07
ABC 567 Oct-07
ABC 567 Nov-07
ABC 567 Dec-07
PQRS 897 Jul-07
PQRS 897 Aug-07
PQRS 897 Sep-07
PQRS 897 Oct-07
PQRS 897 Nov-07
PQRS 897 Dec-07

For that, I am using cursors, I first find the distinct cust,product,month into master_cursor

And then add the missing months in the inner_cursor from Jul-07 to Dec-07.

This just example, I have lot of other columns also involved in this table.

I have the month list in separate table.

The master_cursor has about 25k records, and keep growing.

I would like know alternate suggestion has this taking longer time.

Any suggestion is appreciated.

thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 22:05:17

[code]INSERT INTO table1 (cust, product, [MONTH])
SELECT a.cust, a.product, m.[MONTH]
FROM (
SELECT cust, product
FROM table1
GROUP BY cust, product
) a
CROSS JOIN month_table m
left JOIN table1 t
ON a.cust = t.cust
AND a.product = t.product
AND m.[MONTH] = t.[MONTH]
WHERE t.[MONTH] IS NULL
ORDER BY a.cust, a.product[/code]

KH
[spoiler]Time is always against us[/spoiler]

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-07-20 : 13:47:56

Hi,

Thanks for your reply. It worked like charm.

But I get the issue when I try to insert into the table variable. It takes the same time as the cursor to run. I use the table variable because there are lot more calculation done to other columns.

INSERT INTO @tr_table
select a.prodid,a.custid,a.spid, m.monthid,0,0,0,0,t.custOriginalID,t.prodOriginalID,0

FROM (

SELECT DISTINCT prodid,custid,spid from @tr_table) a

CROSS join @timeid m
left join @trForecast t
ON t.custid = a.custid
AND t.prodid = a.prodid
AND t.spid = a.spid
AND m.monthid = t.monthid
WHERE m.monthcount > 0 and m.monthid not in (select monthid from @@tr_table tr where tr.custid = t.custid and tr.prodid = t.prodid and tr.spid = t.spid)

thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-20 : 22:38:42

you have one extra @ there

(select monthid from @@tr_table tr where tr.custid = t.custid and tr.prodid = t.prodid and tr.spid = t.spid)

KH
[spoiler]Time is always against us[/spoiler]

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-07-23 : 15:10:38

Thanks for your feedback.

It worked great with #temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources