follow my cursor :p

[posol]

if you don't get SQL, get the hell out while you still can


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.

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


are you a nerd?

 

[kaoskustoms]

wtf i am so lost

 

[posol]

thats why i make the medium bucks

the big bucks are coming... someday hehe

doesn't look like anyone else cares either

 

[Battle Pope]

probably because there are maybe 3 people here that understand that.

I'm not one of them.

Congrats anyway

 

[spectacle]

Quoted post[/post]]
probably because there are maybe 3 people here that understand that.

I'm not one of them.

Congrats anyway

 

[EGProject]

Geeks for t3h win.

 

[reikoshea]

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.

 

[posol]

ok, i'll break it down for you

// denote my comments

//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?

 

[reikoshea]

Id just never heard of FETCH before. I googled it, and it made everything clear. I was like...WTF IS HE DOING!!!