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_cursor
On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> 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.googlegr oups.com...
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,
|||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:
>
>
>
>
>
>
> 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?

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:[vbcol=seagreen]
> 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...
>
>
>
>
>
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:
> 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:
>
>
>
>
>
>
>
>
>
>
> 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?

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?

cursor to insert into a table variable

I would appreciate some help in writing a cursor to populate a table variable based on a result set
All the cursor does is find all rows with same area ID and areaName and insert into the table variable the one with the latest modified date, if all modified dates are equal then insert the one with the latest created date.
This is the result set(id int, areaID int, areaName varchar(30), created datetime, modifieddatetime):
3 10 MA 2005-03-04 2005-03-04
6 10 MA 2005-05-04 2005-03-04
9 10 MA 2005-03-04 2005-03-04
1 52 PUERTO RICO 2005-03-04 2005-03-04
2 52 PUERTO RICO 2005-03-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
8 52 PUERTO RICO 2005-03-04 2005-03-04
5 5 NY 2005-04-04 2005-03-04
7 5 NY 2005-03-04 2005-03-04
table variable @.tempTable(id int, areaID int, areaName varchar(30), created datetime, modified datetime)
After running this cursor my temp table would contain:
6 10 MA 2005-05-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
7 5 NY 2005-03-04 2005-03-04
Any help would be appreciated

you don't need a cursor for this at all
this should do it, change YourTable to your table name

insert into @.tempTable(id ,areaID ,areaName , created , modified )
select t1.* from
YourTable t1 join(
select areaID ,areaName , max(created)as MaxCreated,max( modified) as MaxModified
from YourTable
group by areaID ,areaName) t2 on
t1.areaID = t2.areaID
and t1.areaName = t2.areaName
and t1.created = t2.MaxCreated
and t1.modified = t2.MaxModified

if it is possible that you can have created and modified that are the same for an area then you have to group on t1 and grab the min or max id

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

The following query will do..

Select TwoCol.* From
TwoCol
Join
(
Select
IdWise.ColOne,
IdMaxModdate,
IdMaxCrdDate
From

(Select ColOne,ModifieddateTime,Count(*) ModDateCount from TwoCol
group By ColOne,ModifieddateTime) as ModDateWise

Join (Select ColOne,Count(*) IdCount,max(ModifieddateTime) IdMaxModdate, max(createddatetime) IdMaxCrdDate
From TwoCol Group By ColOne) as IdWise On ModDateWise.ColOne = IdWise.ColOne And IdMaxModdate = ModifieddateTime
) as T On T.ColOne = TwoCol.ColOne And ModifieddateTime = IdMaxModdate And createddatetime = IdMaxCrdDate

Cursor doesn't loop properly??

Can someone please tell me why this doesn't work?
I am trying to loop through a table and populate a column called
LocalIDNumber with random numbers... But there must not be any duplicates!
This runs perfectly but only populates the first record and not the other
5000 records in the database...
PLEASE, PLEASE Help...
Thanks
...SQL...
DECLARE RandomCursor CURSOR FOR
SELECT personNo FROM person
OPEN RandomCursor
--Get a variable for the id of the record we are going 2 update
DECLARE @.IDField as int
-- Perform the first fetch.
FETCH NEXT FROM RandomCursor
-- Get the data from the cursor into local variables
INTO @.IDField
-- Check @.@.FETCH_STATUS to see if there are any more ros to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM RandomCursor
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
-- This will create a random number between 10000 and 99999
SET @.Lower = 10000 -- The lowest random number
SET @.Upper = 99999 -- The highest random number
SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
UPDATE
person
SET LocalIDNumber = @.Random
WHERE personNo= @.IDField
END
CLOSE RandomCursor
DEALLOCATE RandomCursorshouldn't this
FETCH NEXT FROM RandomCursor
--Something is missing here!!!!!!!!!!!!!!!!!!!
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
be
FETCH NEXT FROM RandomCursor
INTO @.IDField -- Here we go ;-)
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
Denis the SQL Menace
http://sqlservercode.blogspot.com/
.. wrote:
> Can someone please tell me why this doesn't work?
> I am trying to loop through a table and populate a column called
> LocalIDNumber with random numbers... But there must not be any duplicates!
> This runs perfectly but only populates the first record and not the other
> 5000 records in the database...
> PLEASE, PLEASE Help...
> Thanks
> ...SQL...
> DECLARE RandomCursor CURSOR FOR
> SELECT personNo FROM person
>
> OPEN RandomCursor
> --Get a variable for the id of the record we are going 2 update
> DECLARE @.IDField as int
> -- Perform the first fetch.
> FETCH NEXT FROM RandomCursor
>
> -- Get the data from the cursor into local variables
> INTO @.IDField
> -- Check @.@.FETCH_STATUS to see if there are any more ros to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM RandomCursor
> DECLARE @.Random int;
> DECLARE @.Upper int;
> DECLARE @.Lower int
> -- This will create a random number between 10000 and 99999
> SET @.Lower = 10000 -- The lowest random number
> SET @.Upper = 99999 -- The highest random number
> SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
> UPDATE
> person
> SET LocalIDNumber = @.Random
> WHERE personNo= @.IDField
> END
> CLOSE RandomCursor
> DEALLOCATE RandomCursor|||"Tim::.." <myatix_at_hotmail.com> wrote in message
news:31C2DFA2-38E6-4217-B597-5C0F9F1CD326@.microsoft.com...
> Can someone please tell me why this doesn't work?
> I am trying to loop through a table and populate a column called
> LocalIDNumber with random numbers... But there must not be any duplicates!
> This runs perfectly but only populates the first record and not the other
> 5000 records in the database...
> PLEASE, PLEASE Help...
> Thanks
> ...SQL...
> DECLARE RandomCursor CURSOR FOR
SELECT personNo FROM person
OPEN RandomCursor
--Get a variable for the id of the record we are going 2 update
DECLARE @.IDField as int
-- Perform the first fetch.
-- Get the data from the cursor into local variables
FETCH NEXT FROM RandomCursor INTO @.IDField
-- Check @.@.FETCH_STATUS to see if there are any more ros to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
DECLARE @.Random int;
DECLARE @.Upper int;
DECLARE @.Lower int
-- This will create a random number between 10000 and 99999
SET @.Lower = 10000 -- The lowest random number
SET @.Upper = 99999 -- The highest random number
SELECT @.Random = Round(((@.Upper - @.Lower -1) * Rand() + @.Lower), 0)
UPDATE
person
SET LocalIDNumber = @.Random
WHERE personNo= @.IDField
FETCH NEXT FROM RandomCursor INTO @.IDField
END
David|||you missed INTO @.IDField clause in your second fetch. Note that you can
get duplicates. To get rid of them, use this:
select
1 id, 1 rand_num
into #t
union all
select 2, 0
union all
select 3, 5
union all
-- rand() generated a duplicate
select 4, 1
union all
select 5, 17
union all
select 6, 3
union all
select 7, 13
union all
select 8, 9
union all
select 9, 4
union all
select 10, 21
go
select * from #t
-- remove duplicates
update #t set rand_num = rand_num + (select count(*) from #t t1
where t1.rand_num < #t.rand_num or (t1.rand_num = #t.rand_num and
t1.id < #t.id))
go
select * from #t

Cursor

hey all,
i am relatively new to cursors and have created a SP that uses a cursor to populate a table. here is the code

CREATE PROCEDURE sppa_invvoid
(
@.invno int
)

AS

DECLARE @.glTranKey int
DECLARE @.AcctRefKey int
DECLARE @.BatchKey int
DECLARE @.CreateDate datetime
DECLARE @.CreateType smallint
DECLARE @.CurrExchRate float
DECLARE @.CurrID varchar (3)
DECLARE @.ExtCmnt varchar (255)
DECLARE @.FiscPer smallint
DECLARE @.FiscYear varchar (5)
DECLARE @.GLAcctKey int
DECLARE @.JrnlKey int
DECLARE @.JrnlNo int
DECLARE @.PostAmt decimal(15, 3)
DECLARE @.PostAmtHC decimal(15, 3)
DECLARE @.PostCmnt varchar (50)
DECLARE @.PostDate datetime
DECLARE @.PostQty decimal(16, 8)
DECLARE @.SourceModuleNo smallint
DECLARE @.TranDate datetime
DECLARE @.TranKey int
DECLARE @.TranNo varchar (10)
DECLARE @.TranType int
DECLARE @.Companyid varchar(3)
DECLARE @.Batchtype int
DECLARE @.Userid varchar (30)
DECLARE @.Moduleno int
DECLARE @.NextBatchNo int
DECLARE @._oRetVal int
DECLARE @.iTableName varchar(50)
DECLARE @.iCommitFlag int
DECLARE @.NextJrnlNo int

Set @.CompanyID= 'EMA'
Set @.JrnlNo = 139
Set @.iCommitFlag = 1
Set @.JrnlKey = 193
Set @.iTableName='tgltransaction'
Set @.batchtype = 501
Set @.moduleNo = 5
Set @.Userid = 'Admin'

EXECUTE spGetNextBatchNo @.CompanyID, @.BatchType, @.UserId, @.ModuleNo, @.BatchKey OUTPUT, @.NextBatchNo OUTPUT, @._oRetVal OUTPUT

Execute spglGiveNextJrnlNo @.CompanyID, @.JrnlKey, @.iCommitFlag, @.JrnlNo, @.NextJrnlNo OUTPUT


DECLARE cursor_tran CURSOR FOR

select glTranKey, AcctRefKey,CreateDate,CreateType,CurrExchRate,Curr ID,ExtCmnt,FiscPer,FiscYear,GLAcctKey,JrnlKey,Jrnl No,PostAmt,PostAmtHC,PostCmnt,PostDate,PostQty,Sou rceModuleNo,TranDate,TranKey,TranNo,TranType
from tgltransaction where tranno = @.invno

OPEN cursor_tran

FETCH NEXT FROM cursor_tran INTO

@.glTranKey,
@.AcctRefKey,
@.CreateDate,
@.CreateType,
@.CurrExchRate,
@.CurrID,
@.ExtCmnt,
@.FiscPer,
@.FiscYear,
@.GLAcctKey,
@.JrnlKey,
@.JrnlNo,
@.PostAmt,
@.PostAmtHC,
@.PostCmnt,
@.PostDate,
@.PostQty,
@.SourceModuleNo,
@.TranDate,
@.TranKey,
@.TranNo,
@.TranType

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

BEGIN

Execute spgetnextSurrogateKey @.iTablename , @.glTranKey OUTPUT
/*Execute aaaa*/




INSERT INTO tgltransaction
(glTranKey,
AcctRefKey,
BatchKey,
CreateDate,
CreateType,
CurrExchRate,
CurrID,
ExtCmnt,
FiscPer,
FiscYear,
GLAcctKey,
JrnlKey,
JrnlNo,
PostAmt,
PostAmtHC,
PostCmnt,
PostDate,
PostQty,
SourceModuleNo,
TranDate,
TranKey,
TranNo,
TranType)

Values

(@.glTrankey,
@.AcctRefKey,
@.BatchKey,
@.CreateDate,
@.CreateType,
@.CurrExchRate,
@.CurrID,
@.ExtCmnt,
@.FiscPer,
@.FiscYear,
@.GLAcctKey,
@.JrnlKey,
@.JrnlNo,
@.PostAmt,
@.PostAmtHC,
@.PostCmnt,
@.PostDate,
@.PostQty,
@.SourceModuleNo,
@.TranDate,
@.TranKey,
@.TranNo,
@.TranType)

FETCH NEXT FROM cursor_tran INTO

@.glTranKey,
@.AcctRefKey,
@.CreateDate,
@.CreateType,
@.CurrExchRate,
@.CurrID,
@.ExtCmnt,
@.FiscPer,
@.FiscYear,
@.GLAcctKey,
@.JrnlKey,
@.JrnlNo,
@.PostAmt,
@.PostAmtHC,
@.PostCmnt,
@.PostDate,
@.PostQty,
@.SourceModuleNo,
@.TranDate,
@.TranKey,
@.TranNo,
@.TranType

END
CLOSE cursor_tran
DEALLOCATE cursor_tran
GO

the issue that i am having is after i do the insert, the cursor picks up on the inserted row and it ultimately becomes an infinate loop. what can i do to prevent it from picking up the newly inserted rows. thanks alot

tibornevermind, just saw my problem. of course it will loop if youre using the same table for insert and cursor, lol.|||Glad to see you've figured out how to use cursors. Now do yourself a favor and forget about them, and use set-based processing instead.

Your spglGiveNextJrnlNo procedure should be converted to a user-defined function, (or better yet, dumped altogether), and then you can write your code as a much shorter and much more efficient INSERT statement.|||Hey, a wiseman once said:

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578