#1
  1. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    12

    cursors filling table


    looking for some help on a stored procedure. here is what i have. it is supposed to insert the unique values from an existing table into a new table. should be 40, but instead it continually puts in blank values and won't stop. so here's the proc.
    Code:
    CREATE PROCEDURE sp_filltitletable
    AS
    declare title_curs cursor for
    select title
    from employee group by title
    for read only
    
    declare @actualtitle varchar(50)
    
    open title_curs
    fetch title_curs into @actualtitle
    while @@fetch_status = 0
    begin
    insert into Emp_Title (Title) values (@actualtitle)
    end
    close title_curs
    deallocate title_curs
    figured it out, had the fetch in the wrong spot!
    Last edited by don_sparko; November 10th, 2003 at 09:39 AM.
    My brain cells are like a storm trooper's armor: useless
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Using a cursor is unnecessary, all you need is

    Code:
    insert into Emp_Title (Title) 
    select distinct title from employee
  4. #3
  5. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    12
    swamp. thanks, maybe you can help me with the next step. here is the table structure so you know what i'm trying to do.
    Code:
    employee
    ------------
    title 
    titleID  fk
    
    emp_title
    --------------
    titleID pk
    title
    i'm actually fixing the DB here. the unique titles have been taken from employee and put in emp_title. what i want to do now is put the titleID from emp_title into titleID in employee where employee.title = emp_title.title
    any ideas?
    My brain cells are like a storm trooper's armor: useless
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    update employee
    set titleID = (select titleID
    from emp_title 
    where title = employee.title)
  8. #5
  9. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    12
    thanks worked great!
    My brain cells are like a storm trooper's armor: useless

IMN logo majestic logo threadwatch logo seochat tools logo