How can I avoid using a CURSOR inside of another CURSOR

I am trying to avoid using a CURSOR inside of another CURSOR.

Here's what I have to do...

I have 3 tables:

Table1 has 500,00 rows

Table2 has 25 rows

Table3 is empty

For each row in Table1, I want to INSERT 25 rows (from Table2) into Table3.

Using a CURSOR inside of another CURSOR, my code looks like this...

Define and open CURSORA for TABLE1

FETCH the first row from TABLE1 using CURSORA

WHILE (@.@.FETCH_STATUS<>-1)

Define and open CURSORB for Table2

FETCH the first row from Table2 using CURSORB

WHILE (@.@.FETCH_STATUS<>-1)

INSERT a row into TABLE3 (using the data from Table1 and Table2)

FETCH the next row from Table2 USING CURSORB

CLOSE the CURSORB cursor.

FETCH the next row from Table1 USING CURSORA

CLOSE the CURSORA cursor.

Using this CURSOR inside of another CURSOR is taking way too long.

Is there an SQL technique that I can use to get around using multiple cursors?

TIA!!!

DLdfrd

Use a setbased operation, its hard to guess the solution if you don′t give us more information what you actually want to do.

HTH, jens Suessmeyer.

|||You will probably want to use a Cross Join query -- TableA Cross Join TableB. Make a select statement that returns the columns you desire, then turn it into an Insert/Select.|||

Hey anomulous,

Thanks for the advice...

A Cross Join works very well. The join still takes awhile but one of the tables had 1.7 million rows but the Cross Join is a real big improvement.

This week, I have learned Inner Joins and Left Outer Joins. Today, I learned about Cross Joins.

Thanks again...

how assign value to cursor using sp_executesql procedure

What is error here when i declare cursor ?

declare curQueryVehicleHave cursor for
exec sp_executesql @.strQueryVehicleHave

@.strQueryVehicleHave this string contain a queryMaybe someone can offer a better alternative, but as far as I know you can't do it that way. You will need to place the results of the EXEC into a #temp table and then use the #temp table as the source of your cursor.

At the risk of looking like an idiot, this is my testing code:

DECLARE @.myQuery nvarchar(800)
SET @.myQuery = 'select div_code from division'
DECLARE @.div_code varchar(10)
CREATE Table #Temp (div_code varchar(10))
INSERT INTO #Temp (div_code) EXECUTE sp_executesql @.myQuery -- note that a table variable will not work here
DECLARE curQueryVehicleHave CURSOR FOR SELECT * FROM #TEMP
OPEN curQueryVehicleHave
FETCH NEXT FROM curQueryVehicleHave INTO @.div_code
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.div_code
FETCH NEXT FROM curQueryVehicleHave INTO @.div_code
END
CLOSE curQueryVehicleHave
DEALLOCATE curQueryVehicleHave
DROP TABLE #Temp

Terri|||I hate this about SQL Server. I'd like to see a syntax like

insert into TableName (Columns, ...)
select Columns, ...
from
exec StoredProc

Please SQL Server people?

Hotfix for article 831997

How can I obtain the Hotfix noted in Article 831997.
After I applied 8.00.0859, I am now getting the
error "Invalid Cursor State" when in design mode of the
Enterprise Manager.
Thanks!
Did you actually need to apply 8.00.859? I've found that in most cases
people applied this hotfix merely because it was available to the general
public. The reason hotfixes aren't announced and made more readily
accessible is because they aren't fully regression tested, and aren't immune
to issues like this one.
In any case, you can get 878 from http://support.microsoft.com/?kbid=838166
...
Also, see http://www.aspfaq.com/2515 ... if you stop using Enterprise
Manager for data/schema manipulation, amazingly, the invalid cursor state
error goes away.
http://www.aspfaq.com/
(Reverse address to reply.)
"Troy Anderson" <tanderso@.sonoma-county.org> wrote in message
news:2817301c46384$d0604720$a301280a@.phx.gbl...
> How can I obtain the Hotfix noted in Article 831997.
> After I applied 8.00.0859, I am now getting the
> error "Invalid Cursor State" when in design mode of the
> Enterprise Manager.
> Thanks!
|||If you are needing a hotfix, you'd best contact Microsoft PSS Support and
ask for the hotfix. Hotfixes are grace (free) cases.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Hotfix for article 831997

How can I obtain the Hotfix noted in Article 831997.
After I applied 8.00.0859, I am now getting the
error "Invalid Cursor State" when in design mode of the
Enterprise Manager.
Thanks!Did you actually need to apply 8.00.859? I've found that in most cases
people applied this hotfix merely because it was available to the general
public. The reason hotfixes aren't announced and made more readily
accessible is because they aren't fully regression tested, and aren't immune
to issues like this one.
In any case, you can get 878 from http://support.microsoft.com/?kbid=838166
...
Also, see http://www.aspfaq.com/2515 ... if you stop using Enterprise
Manager for data/schema manipulation, amazingly, the invalid cursor state
error goes away.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Troy Anderson" <tanderso@.sonoma-county.org> wrote in message
news:2817301c46384$d0604720$a301280a@.phx.gbl...
> How can I obtain the Hotfix noted in Article 831997.
> After I applied 8.00.0859, I am now getting the
> error "Invalid Cursor State" when in design mode of the
> Enterprise Manager.
> Thanks!|||If you are needing a hotfix, you'd best contact Microsoft PSS Support and
ask for the hotfix. Hotfixes are grace (free) cases.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Hotfix for article 831997

How can I obtain the Hotfix noted in Article 831997.
After I applied 8.00.0859, I am now getting the
error "Invalid Cursor State" when in design mode of the
Enterprise Manager.
Thanks!Did you actually need to apply 8.00.859? I've found that in most cases
people applied this hotfix merely because it was available to the general
public. The reason hotfixes aren't announced and made more readily
accessible is because they aren't fully regression tested, and aren't immune
to issues like this one.
In any case, you can get 878 from http://support.microsoft.com/?kbid=838166
...
Also, see http://www.aspfaq.com/2515 ... if you stop using Enterprise
Manager for data/schema manipulation, amazingly, the invalid cursor state
error goes away.
http://www.aspfaq.com/
(Reverse address to reply.)
"Troy Anderson" <tanderso@.sonoma-county.org> wrote in message
news:2817301c46384$d0604720$a301280a@.phx
.gbl...
> How can I obtain the Hotfix noted in Article 831997.
> After I applied 8.00.0859, I am now getting the
> error "Invalid Cursor State" when in design mode of the
> Enterprise Manager.
> Thanks!|||If you are needing a hotfix, you'd best contact Microsoft PSS Support and
ask for the hotfix. Hotfixes are grace (free) cases.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Hotfix fixed my SQL Server

I've applied "Hotfix 8.00.0859", which I downloaded from the Microsoft site,
and now I get the "Invalid cursor state" error described here
http://support.microsoft.com/?kbid=831997
The article says there is another hotfix that fixes what the previous hotfix
screwed up, and one should contact "Microsoft Product Support Services" to
get the new hotfix, and give a hyperlink to support where you can pay to
call MS, etc, which I don't really intend to do.
Anyone knows of a better way to get this new hotfix, have a link to it
maybe? I've downloaded the previous one, don't understand why this one is so
special.
Thanks.> give a hyperlink to support where you can pay to
> call MS, etc, which I don't really intend to do.
I suppose you've never done this before. If it's a bug in the product, and
they provide you with a fix, you are not charged for the call. In any
case...

> maybe? I've downloaded the previous one, don't understand why this one is
> so special.
Most hotfixes are not freely available because, as the article always
states, it is only intended to fix the specific problem for those sites that
are having the problem (e.g., not everyone and their brother). The reason
the hotfixes aren't handed out to everyone is because they are not fully
regression tested, and could possibly introduce other problems (e.g.
"Invalid Cursor State").
In your case, there is a newer hotfix that is publicly available. See the
end of http://www.aspfaq.com/2515
I would *STRONGLY* recommend, in the future, that you do not apply hotfixes
just because they are available to download from the Microsoft web site.
http://www.aspfaq.com/
(Reverse address to reply.)|||Aaron, thanks for the link, I am going to give the hotfix a shot.
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
Actually I've been programming with SQL Server for 10 years now and this was
my first hotfix ever, applied last Sunday after fighting a Report Server
install for about six hours, and someone that had the problem gave the
advice. In the end it was something else, but after six hours you don't ask
questions any more, I was ready for a total SQL Server re-install.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:unm3O0wXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> I suppose you've never done this before. If it's a bug in the product,
and
> they provide you with a fix, you are not charged for the call. In any
> case...
>
is[vbcol=seagreen]
> Most hotfixes are not freely available because, as the article always
> states, it is only intended to fix the specific problem for those sites
that
> are having the problem (e.g., not everyone and their brother). The reason
> the hotfixes aren't handed out to everyone is because they are not fully
> regression tested, and could possibly introduce other problems (e.g.
> "Invalid Cursor State").
> In your case, there is a newer hotfix that is publicly available. See the
> end of http://www.aspfaq.com/2515
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Hi Aaron,
The link you gave me to the hotfix fixed the problem, thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:unm3O0wXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> I suppose you've never done this before. If it's a bug in the product,
and
> they provide you with a fix, you are not charged for the call. In any
> case...
>
is[vbcol=seagreen]
> Most hotfixes are not freely available because, as the article always
> states, it is only intended to fix the specific problem for those sites
that
> are having the problem (e.g., not everyone and their brother). The reason
> the hotfixes aren't handed out to everyone is because they are not fully
> regression tested, and could possibly introduce other problems (e.g.
> "Invalid Cursor State").
> In your case, there is a newer hotfix that is publicly available. See the
> end of http://www.aspfaq.com/2515
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

Hotfix fixed my SQL Server

I've applied "Hotfix 8.00.0859", which I downloaded from the Microsoft site,
and now I get the "Invalid cursor state" error described here
http://support.microsoft.com/?kbid=831997
The article says there is another hotfix that fixes what the previous hotfix
screwed up, and one should contact "Microsoft Product Support Services" to
get the new hotfix, and give a hyperlink to support where you can pay to
call MS, etc, which I don't really intend to do.
Anyone knows of a better way to get this new hotfix, have a link to it
maybe? I've downloaded the previous one, don't understand why this one is so
special.
Thanks.
> give a hyperlink to support where you can pay to
> call MS, etc, which I don't really intend to do.
I suppose you've never done this before. If it's a bug in the product, and
they provide you with a fix, you are not charged for the call. In any
case...

> maybe? I've downloaded the previous one, don't understand why this one is
> so special.
Most hotfixes are not freely available because, as the article always
states, it is only intended to fix the specific problem for those sites that
are having the problem (e.g., not everyone and their brother). The reason
the hotfixes aren't handed out to everyone is because they are not fully
regression tested, and could possibly introduce other problems (e.g.
"Invalid Cursor State").
In your case, there is a newer hotfix that is publicly available. See the
end of http://www.aspfaq.com/2515
I would *STRONGLY* recommend, in the future, that you do not apply hotfixes
just because they are available to download from the Microsoft web site.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Aaron, thanks for the link, I am going to give the hotfix a shot.
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
Actually I've been programming with SQL Server for 10 years now and this was
my first hotfix ever, applied last Sunday after fighting a Report Server
install for about six hours, and someone that had the problem gave the
advice. In the end it was something else, but after six hours you don't ask
questions any more, I was ready for a total SQL Server re-install.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:unm3O0wXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> I suppose you've never done this before. If it's a bug in the product,
and[vbcol=seagreen]
> they provide you with a fix, you are not charged for the call. In any
> case...
is
> Most hotfixes are not freely available because, as the article always
> states, it is only intended to fix the specific problem for those sites
that
> are having the problem (e.g., not everyone and their brother). The reason
> the hotfixes aren't handed out to everyone is because they are not fully
> regression tested, and could possibly introduce other problems (e.g.
> "Invalid Cursor State").
> In your case, there is a newer hotfix that is publicly available. See the
> end of http://www.aspfaq.com/2515
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Hi Aaron,
The link you gave me to the hotfix fixed the problem, thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:unm3O0wXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> I suppose you've never done this before. If it's a bug in the product,
and[vbcol=seagreen]
> they provide you with a fix, you are not charged for the call. In any
> case...
is
> Most hotfixes are not freely available because, as the article always
> states, it is only intended to fix the specific problem for those sites
that
> are having the problem (e.g., not everyone and their brother). The reason
> the hotfixes aren't handed out to everyone is because they are not fully
> regression tested, and could possibly introduce other problems (e.g.
> "Invalid Cursor State").
> In your case, there is a newer hotfix that is publicly available. See the
> end of http://www.aspfaq.com/2515
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

Hotfix fixed my SQL Server

I've applied "Hotfix 8.00.0859", which I downloaded from the Microsoft site,
and now I get the "Invalid cursor state" error described here
http://support.microsoft.com/?kbid=831997
The article says there is another hotfix that fixes what the previous hotfix
screwed up, and one should contact "Microsoft Product Support Services" to
get the new hotfix, and give a hyperlink to support where you can pay to
call MS, etc, which I don't really intend to do.
Anyone knows of a better way to get this new hotfix, have a link to it
maybe? I've downloaded the previous one, don't understand why this one is so
special.
Thanks.> give a hyperlink to support where you can pay to
> call MS, etc, which I don't really intend to do.
I suppose you've never done this before. If it's a bug in the product, and
they provide you with a fix, you are not charged for the call. In any
case...
> maybe? I've downloaded the previous one, don't understand why this one is
> so special.
Most hotfixes are not freely available because, as the article always
states, it is only intended to fix the specific problem for those sites that
are having the problem (e.g., not everyone and their brother). The reason
the hotfixes aren't handed out to everyone is because they are not fully
regression tested, and could possibly introduce other problems (e.g.
"Invalid Cursor State").
In your case, there is a newer hotfix that is publicly available. See the
end of http://www.aspfaq.com/2515
I would *STRONGLY* recommend, in the future, that you do not apply hotfixes
just because they are available to download from the Microsoft web site.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Aaron, thanks for the link, I am going to give the hotfix a shot.
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
Actually I've been programming with SQL Server for 10 years now and this was
my first hotfix ever, applied last Sunday after fighting a Report Server
install for about six hours, and someone that had the problem gave the
advice. In the end it was something else, but after six hours you don't ask
questions any more, I was ready for a total SQL Server re-install.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:unm3O0wXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > give a hyperlink to support where you can pay to
> > call MS, etc, which I don't really intend to do.
> I suppose you've never done this before. If it's a bug in the product,
and
> they provide you with a fix, you are not charged for the call. In any
> case...
> > maybe? I've downloaded the previous one, don't understand why this one
is
> > so special.
> Most hotfixes are not freely available because, as the article always
> states, it is only intended to fix the specific problem for those sites
that
> are having the problem (e.g., not everyone and their brother). The reason
> the hotfixes aren't handed out to everyone is because they are not fully
> regression tested, and could possibly introduce other problems (e.g.
> "Invalid Cursor State").
> In your case, there is a newer hotfix that is publicly available. See the
> end of http://www.aspfaq.com/2515
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Hi Aaron,
The link you gave me to the hotfix fixed the problem, thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:unm3O0wXEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > give a hyperlink to support where you can pay to
> > call MS, etc, which I don't really intend to do.
> I suppose you've never done this before. If it's a bug in the product,
and
> they provide you with a fix, you are not charged for the call. In any
> case...
> > maybe? I've downloaded the previous one, don't understand why this one
is
> > so special.
> Most hotfixes are not freely available because, as the article always
> states, it is only intended to fix the specific problem for those sites
that
> are having the problem (e.g., not everyone and their brother). The reason
> the hotfixes aren't handed out to everyone is because they are not fully
> regression tested, and could possibly introduce other problems (e.g.
> "Invalid Cursor State").
> In your case, there is a newer hotfix that is publicly available. See the
> end of http://www.aspfaq.com/2515
> I would *STRONGLY* recommend, in the future, that you do not apply
hotfixes
> just because they are available to download from the Microsoft web site.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||I have the same problem. Did you get a fix?
Thanks!
~ Troy
>--Original Message--
>I've applied "Hotfix 8.00.0859", which I downloaded from
the Microsoft site,
>and now I get the "Invalid cursor state" error described
here
>http://support.microsoft.com/?kbid=831997
>The article says there is another hotfix that fixes what
the previous hotfix
>screwed up, and one should contact "Microsoft Product
Support Services" to
>get the new hotfix, and give a hyperlink to support where
you can pay to
>call MS, etc, which I don't really intend to do.
>Anyone knows of a better way to get this new hotfix, have
a link to it
>maybe? I've downloaded the previous one, don't understand
why this one is so
>special.
>Thanks.
>
>.
>|||> I have the same problem. Did you get a fix?
Did you read the rest of the thread you replied to?|||The direct link to the fix that fixed it for me (thanks to Aaron) is
http://support.microsoft.com/?kbid=838166
"Troy Anderson" <tanderso@.sonoma-county.org> wrote in message
news:322801c46385$3df4f380$3a01280a@.phx.gbl...
> I have the same problem. Did you get a fix?
> Thanks!
> ~ Troy
> >--Original Message--
> >I've applied "Hotfix 8.00.0859", which I downloaded from
> the Microsoft site,
> >and now I get the "Invalid cursor state" error described
> here
> >http://support.microsoft.com/?kbid=831997
> >
> >The article says there is another hotfix that fixes what
> the previous hotfix
> >screwed up, and one should contact "Microsoft Product
> Support Services" to
> >get the new hotfix, and give a hyperlink to support where
> you can pay to
> >call MS, etc, which I don't really intend to do.
> >Anyone knows of a better way to get this new hotfix, have
> a link to it
> >maybe? I've downloaded the previous one, don't understand
> why this one is so
> >special.
> >
> >Thanks.
> >
> >
> >.
> >