Assign a cursor value to a table column

Asked

Viewed 667 times

1

I have a cursor. I need an Insert to insert the value of each cursor position into a table. How do I do this? I need to enter the cursor value exame_cur. My code below:

set nocount on
declare 
@id_xfc int,
@id_exm_rea int,
@id_oit int

declare exame_cur cursor
 for
 select distinct er.id_exm_rea from t_cmo_Exame_Realizado er 
 inner join t_cmo_planilha_leitura pl on er.ID_XFC = pl.ID_XFC
 inner join t_cmo_exame ex on er.id_exm = ex.ID_EXM
 where er.id_exm = 3936 and pl.NO_EXM = 'TÓRAX: P.A.' and er.NO_RX in(select pl.RX_NUM from t_cmo_planilha_leitura pl)
 order by er.id_exm_rea

 open exame_cur
 fetch next from exame_cur into @id_exm_rea

 select max(id_oit) as id_oit into t_id_oit_1 from t_cmo_oit1980

 --insert into t_cmo_oit1980_temp1(id_oit) select id_oit from t_id_oit_1

 while @@fetch_status = 0
 begin
    insert into t_cmo_oit1980_temp1
    select
    (select id_oit + 1 from t_cmo_oit1980_temp1),
    (@id_exm_rea)

 end

 CLOSE exame_cur
 DEALLOCATE exame_cur

select * from t_cmo_oit1980_temp1
go

My code above is correct?

I’ve updated the code to the above, and it’s taking quite a while to execute. I posted these lines and it’s not finished yet.

sql sql-server sql-server-2014

  • I didn’t understand your question, you want to enter the data that are on the cursor? if that is just use the variavies, for example insert into t_cmo_oit1980_temp1 values (@id_exm_rea). If it’s another question, please explain a little better.

  • That’s right, it turns out that when I went to insert, I had errors and did not insert, but the system opened the cursor and now I can not close. Whenever I try to insert gives msg that there is already an open cursor and do not know how I close the cursor on the outside.

  • I executed the close and deallocate sepadao, outside the block and solved.

  • It’s taking too long and you’re not carrying anything. What I want is to only generate records in the eight1980 table, generating the id_oit(PK) which is max + 1 and insert for each id the id_exm_rea, which is what comes from the cursor. Now if there was another way to insert, without the need for the cursor, I would be happy.

  • It went into infinite loop and repeated the same record almost 1 million times.

  • Yes, you need to repeat the command fetch to get the next record. You must repeat the fetch before the end of your loop

  • @Ricardopunctual, before the end I would do it? fetch next from exame_cur into @id_exm_rea

  • Ricardo, that was it. Now it worked. Turn your comment into a reply, so I close the post.

  • Okay, responded. Thank you @pnet

Show 4 more comments

1 answer

0

Only fetch variable loaded with value in insert and do the fetch again inside the loop while:

while @@fetch_status = 0
begin
    -- aqui, verificar se o insert está correto usando @id_exm_rea
    insert into t_cmo_oit1980_temp1 values (@id_exm_rea)

    -- aqui pega o próximo valor do cursor
    fetch next from exame_cur into @id_exm_rea
end

Browser other questions tagged sql sql-server sql-server-2014

You are not signed in. Login or sign up in order to post.