Hello Folks...

I have a little problem.  I have a table (900,000 rows).  I need to select x
rows based on a field value( no problem so far).

I set up a cursor to get the data ... declare cursor JJJ  fpr Select * from
x where y = '1'. 

Next fpr every record I need to add 1 to XXXX new rows to the table based on
other values in the row... problem... I cannot seem to access any column data
from the cursor.   There are many columns in the table (well about 50 or so).

The Query Anal.. says the procedure is fine until I try to access the
individual fields from the cursor.

So what am I doing wrong... why cannot I access the values as jjj.field1 or
something ... I am sure  this is as simple as it seems. 

(and do not ask why an MCSE is working on a production Database problem)

Thanks a bunch Guys!

I think I got my answer from another post... it seems that I will have to
select data into EVERY field and write it back into the new derived records
(rows)... That is a bunch of typing all of those fields.  But ai guess I will
have to do it.

Show quoteHide quote

In the Query Analyser Object Browser (press F8) find your table in the 'User
Tables' secion, right-click it, and press:

'Script Object to New Window As' > 'Select'

or Insert.  Don't type.  Use your tools.  Besides, why are you using a
cursor?  Can't you just do an INSERT based on the current table?

eg INSERT INTO tbl_test ( test_id, name etc )
SELECT test_id * 10, name + ' etc'
FROM tbl_test

Post the DDL and we'll have a look.


Damien

Show quoteHide quote

Sounds like you just need an INSERT statement rather than a cursor. I
don't understand why you think you need a cursor to do this.

If you need more help please post DDL, sample data, required results.

Geltlemen, gentlemen, gentlemen....

I need a cursor because I have to select a few thousand records out of the
database and based on data in EACH Individual record, I have to create from 1
to several thousand new records.... the only way that I know to move through
the database like that... record by selected record is with a cursor. 

What I did not want to have to do was to DEFINE EVERY column in the table as
a local variable to write to the new table.  But... anyway... the question
was how could I access the data AFTER i pulled it into the cursor.  My
original definition was with a ' Select *'.  I guess I will have to do a
'Select  column, column.....'



Show quoteHide quote

I understand what you are saying but you have missed some critical
information from your explanation. If you are inserting some new rows
into this table then you must be generating the data from somewhere. At
the very least you'll want to change one column in each new row in
order to generate a new candidate key for that row. So what I'm
suggesting is that you should use an INSERT and a JOIN to whatever is
the source for this data,  instead of using a cursor.

Now in the absence of any other information I'm guessing that the
cursor is actually generating this additional data in a loop. In that
case you should consider using an auxiliary table. For example, a table
of numbers from 1 to some arbitrarily large number, joined like this:

INSERT INTO YourTable ( ... )
SELECT T. /* columns unspecified */,
  N.num
  FROM Numbers AS N, YourTable AS T
  WHERE x <= N.num
  /* x is some value you didn't explain,
     probably a column in YourTable */

This is perhaps a pretty strange thing to do, especially given the size
of the insert you are proposing, but without further information on how
you are generating this data it's just an example. If some other tables
are involved then they would probably go in place of the "Numbers"
table in this example.

Case Statement?
View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
I'm confused need some help ... with Union