Cursor Statement

I'm trying to get the cursor to select one record at a time until the infobase
timestamp is not null so that I can get the identity value from each record
instead of just the last one inserted.

"KevinV" <kevjv@hotmail.com> wrote:
>
>It looks like your selecting all 877 records again for each record you get
>from the cursor. What is the select statement inside your cursor loop supposed
>to accomplish?
>
>"sheryl kemp" <dianedinero@aol.com> wrote:
>>
>>
>>I am attempting to select records from a table that currently has 877 records
>>in it, when using the following statement, I get 877 * 877 records. what
>>am I doing wrong
>>
>>declare @ContactName varchar(60)
>>declare contactidcursor cursor for
>>SELECT CLIENT_NME
>>FROM dbo.T_STAGE_NEW_REQUEST
>>WHERE INFOBASE_TIMESTMP IS NULL
>>open contactidcursor
>>fetch NEXT from contactidcursor into @ContactName
>>WHILE @@FETCH_STATUS=0
>>BEGIN
>>SELECT CLIENT_NME FROM dbo.T_STAGE_NEW_REQUEST
>>WHERE INFOBASE_TIMESTMP IS NULL
>>FETCH NEXT FROM contactidcursor into @ContactName
>>END
>>
>>CLOSE contactidcursor
>>DEALLOCATE contactidcursor
>

whoops 1 change:

declare @ContactName varchar(60)
SET ROWCOUNT 1
SELECT @ContactName = CLIENT_NME
FROM dbo.T_STAGE_NEW_REQUEST
WHERE INFOBASE_TIMESTMP IS NULL
SET ROWCOUNT 0

WHILE @ContactName IS NOT NULL
BEGIN

-- do whatever you want to do and move to next 1

SET @ContactName = null -- or else endless loop
SET ROWCOUNT 1
SELECT @ContactName = CLIENT_NME
FROM dbo.T_STAGE_NEW_REQUEST
WHERE INFOBASE_TIMESTMP IS NULL
SET ROWCOUNT 0

END

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote in message
news:3c8cd4c1$1@10.1.10.29...
> declare @ContactName varchar(60)
> SET ROWCOUNT 1
> SELECT @ContactName = CLIENT_NME
> FROM dbo.T_STAGE_NEW_REQUEST
> WHERE INFOBASE_TIMESTMP IS NULL
> SET ROWCOUNT 0
>
> WHILE @ContactName IS NOT NULL
> BEGIN
>
> -- do whatever you want to do and move to next 1
>
> SET ROWCOUNT 1
> SELECT @ContactName = CLIENT_NME
> FROM dbo.T_STAGE_NEW_REQUEST
> WHERE INFOBASE_TIMESTMP IS NULL
> SET ROWCOUNT 0
>
> END
> --
> HTH,
> David Satz
> Principal Web Engineer
> Hyperion Solutions
> { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS }
> (Please reply to group only - emails answered rarely)
> --------------------
> "SHERYL KEMP" <DIANEDINERO@AOL.COM> wrote in message
> news:3c8cd144$1@10.1.10.29...
> >
> > I'm trying to get the cursor to select one record at a time until the
> infobase
> > timestamp is not null so that I can get the identity value from each
> record
> > instead of just the last one inserted.
> >
> >
> > "KevinV" <kevjv@hotmail.com> wrote:
> > >
> > >It looks like your selecting all 877 records again for each record you
> get
> > >from the cursor. What is the select statement inside your cursor loop
> supposed
> > >to accomplish?
> > >
> > >"sheryl kemp" <dianedinero@aol.com> wrote:
> > >>
> > >>
> > >>I am attempting to select records from a table that currently has 877
> records
> > >>in it, when using the following statement, I get 877 * 877 records.
what
> > >>am I doing wrong
> > >>
> > >>declare @ContactName varchar(60)
> > >>declare contactidcursor cursor for
> > >>SELECT CLIENT_NME
> > >>FROM dbo.T_STAGE_NEW_REQUEST
> > >>WHERE INFOBASE_TIMESTMP IS NULL
> > >>open contactidcursor
> > >>fetch NEXT from contactidcursor into @ContactName
> > >>WHILE @@FETCH_STATUS=0
> > >>BEGIN
> > >>SELECT CLIENT_NME FROM dbo.T_STAGE_NEW_REQUEST
> > >>WHERE INFOBASE_TIMESTMP IS NULL
> > >>FETCH NEXT FROM contactidcursor into @ContactName
> > >>END
> > >>
> > >>CLOSE contactidcursor
> > >>DEALLOCATE contactidcursor
> > >
> >
>
>

Ok, table1 is a linked server table referenced using TNF2..KZRMS1.INFOBASEEXPORT,
this is also where the data is inserted by the users. table2 is the receiving
table called T_STAGE_NEW_REQUESTS (table1 and table2 have the exact same
data fields). table3 is a Contacts table for which only certain fields of
data from table1 will be extracted and inserted into this table. when the
two inserts happen, one into table1 and two into table2, I need the identity
value from each of these tables inserted into a third table.

Thanks for your help!

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>not sure - need more details about the 2 tables...
>
>"sheryl kemp" <dianedinero@aol.com> wrote in message
>news:3c8cd92c$1@10.1.10.29...
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >whoops 1 change:
>> >
>> >declare @ContactName varchar(60)
>> >SET ROWCOUNT 1
>> >SELECT @ContactName = CLIENT_NME
>> Thanks so much ! You guys are heros!! One more thing..If I have 2 tables
>> that I want to do this very same thing with and then obtain the identity
>> values from each table to insert into a third table, is there a way that
>> I can include that into this same statement?
>>
>>
>> >FROM dbo.T_STAGE_NEW_REQUEST
>> >WHERE INFOBASE_TIMESTMP IS NULL
>> >SET ROWCOUNT 0
>> >
>> >WHILE @ContactName IS NOT NULL
>> >BEGIN
>> >
>> > -- do whatever you want to do and move to next 1
>> >
>> > SET @ContactName = null -- or else endless loop
>> > SET ROWCOUNT 1
>> > SELECT @ContactName = CLIENT_NME
>> > FROM dbo.T_STAGE_NEW_REQUEST
>> > WHERE INFOBASE_TIMESTMP IS NULL
>> > SET ROWCOUNT 0
>> >
>> >END
>> >
>> >"David Satz" <davidNOSPAMsatz@yahoo.com> wrote in message
>> >news:3c8cd4c1$1@10.1.10.29...
>> >> declare @ContactName varchar(60)
>> >> SET ROWCOUNT 1
>> >> SELECT @ContactName = CLIENT_NME
>> >> FROM dbo.T_STAGE_NEW_REQUEST
>> >> WHERE INFOBASE_TIMESTMP IS NULL
>> >> SET ROWCOUNT 0
>> >>
>> >> WHILE @ContactName IS NOT NULL
>> >> BEGIN
>> >>
>> >> -- do whatever you want to do and move to next 1
>> >>
>> >> SET ROWCOUNT 1
>> >> SELECT @ContactName = CLIENT_NME
>> >> FROM dbo.T_STAGE_NEW_REQUEST
>> >> WHERE INFOBASE_TIMESTMP IS NULL
>> >> SET ROWCOUNT 0
>> >>
>> >> END
>> >> --
>> >> HTH,
>> >> David Satz
>> >> Principal Web Engineer
>> >> Hyperion Solutions
>> >> { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2
} {
>> >VSS }
>> >> (Please reply to group only - emails answered rarely)
>> >> --------------------
>> >> "SHERYL KEMP" <DIANEDINERO@AOL.COM> wrote in message
>> >> news:3c8cd144$1@10.1.10.29...
>> >> >
>> >> > I'm trying to get the cursor to select one record at a time until
the
>> >> infobase
>> >> > timestamp is not null so that I can get the identity value from each
>> >> record
>> >> > instead of just the last one inserted.
>> >> >
>> >> >
>> >> > "KevinV" <kevjv@hotmail.com> wrote:
>> >> > >
>> >> > >It looks like your selecting all 877 records again for each record
>> you
>> >> get
>> >> > >from the cursor. What is the select statement inside your cursor
>loop
>> >> supposed
>> >> > >to accomplish?
>> >> > >
>> >> > >"sheryl kemp" <dianedinero@aol.com> wrote:
>> >> > >>
>> >> > >>
>> >> > >>I am attempting to select records from a table that currently has
>> 877
>> >> records
>> >> > >>in it, when using the following statement, I get 877 * 877 records.
>> >what
>> >> > >>am I doing wrong
>> >> > >>
>> >> > >>declare @ContactName varchar(60)
>> >> > >>declare contactidcursor cursor for
>> >> > >>SELECT CLIENT_NME
>> >> > >>FROM dbo.T_STAGE_NEW_REQUEST
>> >> > >>WHERE INFOBASE_TIMESTMP IS NULL
>> >> > >>open contactidcursor
>> >> > >>fetch NEXT from contactidcursor into @ContactName
>> >> > >>WHILE @@FETCH_STATUS=0
>> >> > >>BEGIN
>> >> > >>SELECT CLIENT_NME FROM dbo.T_STAGE_NEW_REQUEST
>> >> > >>WHERE INFOBASE_TIMESTMP IS NULL
>> >> > >>FETCH NEXT FROM contactidcursor into @ContactName
>> >> > >>END
>> >> > >>
>> >> > >>CLOSE contactidcursor
>> >> > >>DEALLOCATE contactidcursor
>> >> > >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>
>

no, because there isn't a unique identifier on the tnf2... other than a date
timestamp

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>can you join TNF2..KZRMS1.INFOBASEEXPORT and T_STAGE_NEW_REQUESTS to get
a 1
>to 1 relationship ?
>
>"sheryl kemp" <dianedinero@aol.com> wrote in message
>news:3c8ce798$3@10.1.10.29...
>>
>> Ok, table1 is a linked server table referenced using
>TNF2..KZRMS1.INFOBASEEXPORT,
>> this is also where the data is inserted by the users. table2 is the
>receiving
>> table called T_STAGE_NEW_REQUESTS (table1 and table2 have the exact same
>> data fields). table3 is a Contacts table for which only certain fields
of
>> data from table1 will be extracted and inserted into this table. when
the
>> two inserts happen, one into table1 and two into table2, I need the
>identity
>> value from each of these tables inserted into a third table.
>>
>> Thanks for your help!
>>
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >not sure - need more details about the 2 tables...
>> >
>> >"sheryl kemp" <dianedinero@aol.com> wrote in message
>> >news:3c8cd92c$1@10.1.10.29...
>> >>
>> >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >> >whoops 1 change:
>> >> >
>> >> >declare @ContactName varchar(60)
>> >> >SET ROWCOUNT 1
>> >> >SELECT @ContactName = CLIENT_NME
>> >> Thanks so much ! You guys are heros!! One more thing..If I have 2
>tables
>> >> that I want to do this very same thing with and then obtain the
>identity
>> >> values from each table to insert into a third table, is there a way
>that
>> >> I can include that into this same statement?
>> >>
>> >>
>> >> >FROM dbo.T_STAGE_NEW_REQUEST
>> >> >WHERE INFOBASE_TIMESTMP IS NULL
>> >> >SET ROWCOUNT 0
>> >> >
>> >> >WHILE @ContactName IS NOT NULL
>> >> >BEGIN
>> >> >
>> >> > -- do whatever you want to do and move to next 1
>> >> >
>> >> > SET @ContactName = null -- or else endless loop
>> >> > SET ROWCOUNT 1
>> >> > SELECT @ContactName = CLIENT_NME
>> >> > FROM dbo.T_STAGE_NEW_REQUEST
>> >> > WHERE INFOBASE_TIMESTMP IS NULL
>> >> > SET ROWCOUNT 0
>> >> >
>> >> >END
>> >> >
>> >> >"David Satz" <davidNOSPAMsatz@yahoo.com> wrote in message
>> >> >news:3c8cd4c1$1@10.1.10.29...
>> >> >> declare @ContactName varchar(60)
>> >> >> SET ROWCOUNT 1
>> >> >> SELECT @ContactName = CLIENT_NME
>> >> >> FROM dbo.T_STAGE_NEW_REQUEST
>> >> >> WHERE INFOBASE_TIMESTMP IS NULL
>> >> >> SET ROWCOUNT 0
>> >> >>
>> >> >> WHILE @ContactName IS NOT NULL
>> >> >> BEGIN
>> >> >>
>> >> >> -- do whatever you want to do and move to next 1
>> >> >>
>> >> >> SET ROWCOUNT 1
>> >> >> SELECT @ContactName = CLIENT_NME
>> >> >> FROM dbo.T_STAGE_NEW_REQUEST
>> >> >> WHERE INFOBASE_TIMESTMP IS NULL
>> >> >> SET ROWCOUNT 0
>> >> >>
>> >> >> END
>> >> >> --
>> >> >> HTH,
>> >> >> David Satz
>> >> >> Principal Web Engineer
>> >> >> Hyperion Solutions
>> >> >> { SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2
>> } {
>> >> >VSS }
>> >> >> (Please reply to group only - emails answered rarely)
>> >> >> --------------------
>> >> >> "SHERYL KEMP" <DIANEDINERO@AOL.COM> wrote in message
>> >> >> news:3c8cd144$1@10.1.10.29...
>> >> >> >
>> >> >> > I'm trying to get the cursor to select one record at a time until
>> the
>> >> >> infobase
>> >> >> > timestamp is not null so that I can get the identity value from
>each
>> >> >> record
>> >> >> > instead of just the last one inserted.
>> >> >> >
>> >> >> >
>> >> >> > "KevinV" <kevjv@hotmail.com> wrote:
>> >> >> > >
>> >> >> > >It looks like your selecting all 877 records again for each
>record
>> >> you
>> >> >> get
>> >> >> > >from the cursor. What is the select statement inside your cursor
>> >loop
>> >> >> supposed
>> >> >> > >to accomplish?
>> >> >> > >
>> >> >> > >"sheryl kemp" <dianedinero@aol.com> wrote:
>> >> >> > >>
>> >> >> > >>
>> >> >> > >>I am attempting to select records from a table that currently
>has
>> >> 877
>> >> >> records
>> >> >> > >>in it, when using the following statement, I get 877 * 877
>records.
>> >> >what
>> >> >> > >>am I doing wrong
>> >> >> > >>
>> >> >> > >>declare @ContactName varchar(60)
>> >> >> > >>declare contactidcursor cursor for
>> >> >> > >>SELECT CLIENT_NME
>> >> >> > >>FROM dbo.T_STAGE_NEW_REQUEST
>> >> >> > >>WHERE INFOBASE_TIMESTMP IS NULL
>> >> >> > >>open contactidcursor
>> >> >> > >>fetch NEXT from contactidcursor into @ContactName
>> >> >> > >>WHILE @@FETCH_STATUS=0
>> >> >> > >>BEGIN
>> >> >> > >>SELECT CLIENT_NME FROM dbo.T_STAGE_NEW_REQUEST
>> >> >> > >>WHERE INFOBASE_TIMESTMP IS NULL
>> >> >> > >>FETCH NEXT FROM contactidcursor into @ContactName
>> >> >> > >>END
>> >> >> > >>
>> >> >> > >>CLOSE contactidcursor
>> >> >> > >>DEALLOCATE contactidcursor
>> >> >> > >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>

Although cursor is an expensive way of doing it, this is what should be done(assume
CLIENT_NME is unique for each record):

declare @ContactName varchar(60)
declare contactidcursor cursor for
SELECT CLIENT_NME
FROM dbo.T_STAGE_NEW_REQUEST
WHERE INFOBASE_TIMESTMP IS NULL
open contactidcursor
fetch NEXT from contactidcursor into @ContactName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT CLIENT_NME FROM dbo.T_STAGE_NEW_REQUEST
WHERE INFOBASE_TIMESTMP IS NULL
AND CLIENT_NME = @ContactName
FETCH NEXT FROM contactidcursor into @ContactName
END

CLOSE contactidcursor
DEALLOCATE contactidcursor

"sheryl kemp" <dianedinero@aol.com> wrote:
>
>
>I am attempting to select records from a table that currently has 877 records
>in it, when using the following statement, I get 877 * 877 records. what
>am I doing wrong
>
>declare @ContactName varchar(60)
>declare contactidcursor cursor for
>SELECT CLIENT_NME
>FROM dbo.T_STAGE_NEW_REQUEST
>WHERE INFOBASE_TIMESTMP IS NULL
>open contactidcursor
>fetch NEXT from contactidcursor into @ContactName
>WHILE @@FETCH_STATUS=0
>BEGIN
>SELECT CLIENT_NME FROM dbo.T_STAGE_NEW_REQUEST
>WHERE INFOBASE_TIMESTMP IS NULL
>FETCH NEXT FROM contactidcursor into @ContactName
>END
>
>CLOSE contactidcursor
>DEALLOCATE contactidcursor