Go line by row of a table

Question

Go line by row of a table

Navigation

#1 by (3 votes)

2

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

I need to move the data from this temporary table, line by line, and pick certain fields and insert them into another table, and in that other table, generate a coupon code, with a specific sequence, to complete the process.

I can not simply use the INSERT INTO ... SELECT (as I use in my script in other points)

How can I make this loop catch line-by-line from 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

asked by anonymous 19.12.2016 / 20:40

1 answer

Transform array and String Add a new value to a list

score 3 · Accepted Answer

I think your problem can be solved by using cursor. In it you can do treatments line-by-line 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 study base like:

MSDN.Microsoft

DevMedia

CodigoMaster

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