Traverse row by row of a table

Asked

Viewed 6,162 times

2

I have a temporary table that I fill with the result of a particular query.

I need to go through the data of this temporary table, row by row, and take certain fields and insert them into another table, and in this other table, generate a coupon code, with a specific sequence, to complete the process.

I can’t just use INSERT INTO ... SELECT (as I use in my script at other points)

How can I make this loop to capture row by row from the temporary table?

CREATE TABLE #PontosVPAcimaCem (
    qtd_cupons INT
    ,apuracao_ptsvp NUMERIC(14, 2)
    ,apuracao_mesfch INT
    ,apuracao_anofch INT
    ,apuracao_id_client INT
    ,clients_username NVARCHAR(150)
    )

INSERT INTO #PontosVPAcimaCem (
    qtd_cupons
    ,apuracao_ptsvp
    ,apuracao_mesfch
    ,apuracao_anofch
    ,apuracao_id_client
    ,clients_username
    )
SELECT CAST(a.ptsvp / 100 AS INT)
    ,a.ptsvp
    ,a.mesfch
    ,a.anofch
    ,a.id_client
    ,c.username
FROM t_clients c WITH (NOLOCK)
INNER JOIN gr_apuracao a WITH (NOLOCK) ON c.id = a.id_client
WHERE a.mesfch = @apuracao_mes
    AND a.anofch = @apuracao_ano
    AND a.ptsvp >= @apuracao_pontosvp

SELECT qtd_cupons
    ,apuracao_ptsvp
    ,apuracao_mesfch
    ,apuracao_anofch
    ,apuracao_id_client
    ,clients_username
FROM #PontosVPAcimaCem WITH (NOLOCK)
ORDER BY qtd_cupons DESC
sql sql-server tsql

  • Have you tried using Cursor? With it you can consult and manipulate the information on select from temporary table and do treatments line by line.

  • @Willian didn’t try to use it. I need to learn about it in order to use it, because I’m a beginner in SQL. I would have some article to share, please?

  • You can look at these here: http://www.devmedia.com.br/cursores-no-sqlserver/5590. I will put together an answer to try to simplify because it is a more advanced concept and I myself have had a certain difficulty in understanding

  • 2

    Possible duplicate of Capture values from one table and insert them into another

  • @Josédiz did, in fact. I could not delete the topic you mentioned, unfortunately the title did not match what I needed, I tried to edit it, but it was not possible.

  • @Felipenegro Managed to solve the problem using Cursor?

  • @Felipenegro: How is the coupon code generated? What are the rules for entering the data in the "other table"? What is the name and structure of the "other table"?

Show 2 more comments

1 answer

3


I believe your problem can be solved using cursor. In it you can do line-by-line treatments and implement the treatments you want.

DECLARE @vQtd_cupons varchar(50)
       ,@vApuracao_ptsvp varchar(50)
       ,@vApuracao_mesfch varchar(50)
       ,@vApuracao_anofch varchar(50)
       ,@vApuracao_id_client varchar(50)
       ,@vClients_username varchar(50)

-- Declaração do cursor. Nesse ponto você especifica a qual consulta o cursor irá manipular
DECLARE Crs_teste CURSOR FOR
SELECT qtd_cupons
      ,apuracao_ptsvp
      ,apuracao_mesfch
      ,apuracao_anofch
      ,apuracao_id_client
      ,clients_username
from #tmp_teste

-- Abertura do cursor. Aqui a consulta é feita e o Cursor mantem as informações
OPEN Crs_teste 

-- Recupera a linha do cursor 
FETCH Crs_teste 
-- Define o valor das variáveis com os valores da linha que ele está percorrendo. 
-- É importante que as variáveis estejam na mesma ordem que as colunas na consulta
INTO @vQtd_cupons
    ,@vApuracao_ptsvp
    ,@vApuracao_mesfch
    ,@vApuracao_anofch
    ,@vApuracao_id_client
    ,@vClients_username

-- Loop. O Fetch_Status retorna os valores 0 [Ok], -1[Falha] e -2 [Registro perdido]
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  /* Tratamentos para geração do Código de barras
  ** Insert into em outra tabela
  **
  */

  -- Passa para o proximo registro, caso seja o ultimo registro da consulta, o @@Fetch_status passa a ser -1
  FETCH Crs_teste 
  -- Insere os valores das linhas nas variáveis
  INTO @vQtd_cupons
      ,@vApuracao_ptsvp
      ,@vApuracao_mesfch
      ,@vApuracao_anofch
      ,@vApuracao_id_client
      ,@vClients_username
END
-- Fecha o cursor
CLOSE Crs_teste
-- Remove a referência do cursor. 
DEALLOCATE Crs_teste

You can use some articles as a basis for studies such as :

MSDN.Microsoft

Devmedia

Code

There are other tutorials and articles out there, but I hope the explanation in the code is understandable.

Browser other questions tagged sql sql-server tsql

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