1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

follow my cursor :p

Discussion in 'Computers, Games, Electronics etc' started by pissedoffsol, Nov 3, 2005.

  1. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    54
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    if you don't get SQL, get the hell out while you still can :p


    I was given an excel sheet of Q&A's to populate previously defined tables.

    all well and good, but the tables are split up. 1 for questions, and 1 for answers. So, if i simply inserted the records, i would loose the coorelation between the two tables.

    first step, import the Q&A file into a table called cv_res_ques2
    id, question, answer are the 3 fields. 82 total rows.

    Now, if i just simply imported these, i would loose the relational nature of the answer to each question--- because there CAN be more than 1 answer associated with each question. (one to many).

    I needed to create a cursor loop that would insert the question, then the answer with respect to the question based off of the insertation id.

    here's the SQL code i used to come up with it.


    Code:
    declare @ques varchar(4000)
    declare @ans varchar(4000)
    declare @qid int
    declare @id_list int
    
    declare my_cursor cursor for 
    SELECT [id] AS 'id_list' FROM cv_res_ques2
    OPEN my_cursor
    FETCH NEXT FROM my_cursor into @id_list
    while @@fetch_status = 0
    begin
    
    set @ques = (select question from cv_res_ques2 where id = @id_list)
    set @ans = (select answers from cv_res_ques2 where id = @id_list)
    
    insert into questions
    (requestdate, requestbyID, office, assignedto, product, question, 
    qstatus, closeddate, closedbyid, archiveflag)
    values(
    '11/03/2005 12:00:00 PM', 32, 'Company', 32, NULL, @ques, 
    'RESOLVED', '11/02/2005 12:00:01 PM', 32, NULL
    )
    select @qid = @@identity
    
    insert into answers
    (qid, AnswerDate, answerbyid, answer)
    values(@qid, '11/03/2005 12:01:00 PM', 32, @ans)
    FETCH NEXT FROM my_cursor into @id_list
    end
    CLOSE my_cursor
    DEALLOCATE my_cursor
    

    looped through 82 times, and voila! i hate 2 tables, relational, and my Q&A website is fully functional :p


    are you a nerd?
     
  2. kaoskustoms

    kaoskustoms Senior Member

    Messages:
    550
    Likes Received:
    0
    Joined:
    Jan 6, 2003
    Location:
    casper wyoming man
    wtf i am so lost
     
  3. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    54
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    thats why i make the medium bucks :p

    the big bucks are coming... someday :p hehe

    doesn't look like anyone else cares either :cry2:
     
  4. Battle Pope

    Battle Pope New Member

    Messages:
    10,279
    Likes Received:
    60
    Joined:
    Jun 22, 2004
    Location:
    Southern Oregon Coast
    probably because there are maybe 3 people here that understand that.

    I'm not one of them. :)

    Congrats anyway :p
     
  5. spectacle

    spectacle Senior Member

    Messages:
    898
    Likes Received:
    0
    Joined:
    Aug 23, 2003
    Location:
    St. Petersburg, Fl
    [​IMG]
     
  6. EGProject

    EGProject YEEEEAAHHHHH

    Messages:
    2,870
    Likes Received:
    28
    Joined:
    Jun 26, 2004
    Location:
    Whitinsville, Massachusetts
    Geeks for t3h win.
     
  7. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    I understand most of the middle of the code...but i have never understood how to start something.

    Okay, once you know what the FETCH command does, it makes total sense.
     
  8. pissedoffsol

    pissedoffsol RETIRED

    Messages:
    49,693
    Likes Received:
    54
    Joined:
    Sep 28, 2002
    Location:
    Retirement Home
    ok, i'll break it down for you :p

    // denote my comments


    Code:
    //set up the vars.SQL requires an explicit var definition.
    
    declare @ques varchar(4000)
    declare @ans varchar(4000)
    declare @qid int
    declare @id_list int
    
    // tell it you want a cursor...which is basically a DO-WHILE loop for SQL
    declare my_cursor cursor for 
    //now you need to tell it what the range is.in my case, its a list of id's from my temp table.
    //it will select the id (lets say, 1) and then do the code.then when it repeats, it selects the next (for example, 2)
    SELECT [id] AS 'id_list' FROM cv_res_ques2
    //ok, now we need to open the cursor.this is where we actually do something
    OPEN my_cursor
    //since it starts at 0, you need to actually select the next one... so you start with 1
    FETCH NEXT FROM my_cursor into @id_list
    //@@fetch_status is a system var.basically it tells when to end.when fetch_status is not = 0, the cursor is over, and it exists.if this is over, go below to "end"
    while @@fetch_status = 0
    
    begin
    //let's begin :)begin and end are similar to { } in most languages.
    //here, i set 2 vars to the result of a query.the @id_list is the ID number generated from the cursor incrementation.so, this will grab the question and answer where the id = the cursor id
    set @ques = (select question from cv_res_ques2 where id = @id_list)
    set @ans = (select answers from cv_res_ques2 where id = @id_list)
    //ok, now i need to insert these questions into my previosuly defined tables...
    //@ques comes from the above variable...
    insert into questions
    (requestdate, requestbyID, office, assignedto, product, question, 
    qstatus, closeddate, closedbyid, archiveflag)
    values(
    '11/03/2005 12:00:00 PM', 32, 'Company', 32, NULL, @ques, 
    'RESOLVED', '11/02/2005 12:00:01 PM', 32, NULL
    )
    select @qid = @@identity
    // now, i need to select the question id (@qid).the @@identity system var pulls the id of the row just inserted. i set this to the @qid var (overloading the var) to use for the answer table insert
    
    insert into answers
    (qid, AnswerDate, answerbyid, answer)
    values(@qid, '11/03/2005 12:01:00 PM', 32, @ans)
    //and now we have the question related to the answer...relational databases.:)
    FETCH NEXT FROM my_cursor into @id_list
    //this tells us to pull the next record id and overload the @id_list var (in our case, now we're on 2)
    end
    CLOSE my_cursor
    //close it out
    DEALLOCATE my_cursor
    //get it out of memory and we're done :)
    
    
    does that help?
     
  9. reikoshea

    reikoshea HS Troll...And Mod Moderator VIP

    Messages:
    12,633
    Likes Received:
    193
    Joined:
    Apr 27, 2005
    Location:
    San Antonio, TX
    Id just never heard of FETCH before. I googled it, and it made everything clear. I was like...WTF IS HE DOING!!!
     
Verification:
Draft saved Draft deleted

Share This Page