Cursor to populate a table

Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > Hi!
> > I am trying to create a cursor (please see the code below) that will
> > populate client_all table that has two columns clientid and cid.
> > The first value for that table is drawn from TClient table (@.clientid)
> > and the second one is drawn from get_client_all function (cid).
> > I have to populate client_all table until I reach the end of Tclient
> > table. Tclient table has about 16000 rows. Each of @.clientID might
> > have multiple cids. This cursor runs a long time and doesn't
> > complete.
> > Anybody has any idea?
> > Thanks,
> > declare @.rownumber int
> > declare @.rowcount int
> > select @.rowcount = count(*) from tciclien
> > Declare PopulateTable_cursor Cursor for
> > select idnumber from TClient
> > open PopulateTable_cursor
> > declare @.clientid int
> > Fetch Next from PopulateTable_cursor
> > Into @.clientid
> > WHILE @.@.FETCH_STATUS <> -1
> > Begin
> > while @.rownumber < = @.rowcount
> > Begin
> > insert into Client_all
> > select @.clientid, cd.cid
> > from get_client_all(@.clientid, -1, 0) cd
> > End
> > End
> > close PopulateTable_cursor
> > deallocate PopulateTable_cursor
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>> What does get_client_all do? Can you move away from using cursors and
>> instead just join TClient against the equivalent of whatever this
>> function
>> returns?
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
> > What does get_client_all do? Can you move away from using cursors and
> > instead just join TClient against the equivalent of whatever this function
> > returns?
> > "tolcis" <nytolly...@.gmail.com> wrote in message
> >news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > > Hi!
> > > I am trying to create a cursor (please see the code below) that will
> > > populate client_all table that has two columns clientid and cid.
> > > The first value for that table is drawn from TClient table (@.clientid)
> > > and the second one is drawn from get_client_all function (cid).
> > > I have to populate client_all table until I reach the end of Tclient
> > > table. Tclient table has about 16000 rows. Each of @.clientID might
> > > have multiple cids. This cursor runs a long time and doesn't
> > > complete.
> > > Anybody has any idea?
> > > Thanks,
> > > declare @.rownumber int
> > > declare @.rowcount int
> > > select @.rowcount = count(*) from tciclien
> > > Declare PopulateTable_cursor Cursor for
> > > select idnumber from TClient
> > > open PopulateTable_cursor
> > > declare @.clientid int
> > > Fetch Next from PopulateTable_cursor
> > > Into @.clientid
> > > WHILE @.@.FETCH_STATUS <> -1
> > > Begin
> > > while @.rownumber < = @.rowcount
> > > Begin
> > > insert into Client_all
> > > select @.clientid, cd.cid
> > > from get_client_all(@.clientid, -1, 0) cd
> > > End
> > > End
> > > close PopulateTable_cursor
> > > deallocate PopulateTable_cursor
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?