How to insert records via cursor?

Asked

Viewed 128 times

4

I need to insert unique records by a field that has the unique identifier, so I’m using cursor; however, the unique identifier does not add according to the inserts.

ERROR: You are duplicating and enter only the first record.

See the example below:

USE NESKI_DBA_ERP
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
----------------------------------------------------------------------------
-- Declaração de variáveis que serão atribuidas no insert.

DECLARE 
 @ETQ_ID                          AS INT    ,
 @FIL_CODIGO                      AS NUMERIC,
 @PRO_CODIGO                      AS NUMERIC,
 @ETQ_ESTOQUEFILIAL               AS NUMERIC,
 @ETQ_ESTOQUEFILIALPRODUCAO       AS NUMERIC, 
 @ETQ_ESTOQUEMINIMO               AS NUMERIC,
 @ETQ_ESTOQUEMAXIMO               AS NUMERIC,
 @ETQ_ESTOQUECOMPRADO             AS NUMERIC,
 @ETQ_VALORULTIMOCUSTO            AS NUMERIC,
 @ETQ_VALORCUSTOCOMPRA            AS NUMERIC,
 @ETQ_VALORCUSTOLIQUIDO           AS NUMERIC,
 @ETQ_VALORCUSTOINVENTARIO        AS NUMERIC,
 @ETQ_VALORCUSTOCOMPRAMEDIO       AS NUMERIC,
 @ETQ_VALORCUSTOLIQUIDOMEDIO      AS NUMERIC,
 @ETQ_VALORCUSTOINVENTARIOMEDIO   AS NUMERIC,
 @ETQ_DATAULTIMAVENDA             AS DATE   ,
 @ETQ_VALORULTIMAVENDA            AS NUMERIC,
 @ETQ_QUANTIDADEVENDIDA           AS NUMERIC,
 @ETQ_DATAULTIMACOMPRA            AS DATE   ,
 @ETQ_VALORULTIMACOMPRA           AS NUMERIC,
 @ETQ_QUANTIDADECOMPRADA          AS NUMERIC,
 @ETQ_DATAALTERACAO               AS DATE   ;

 ---------------------------------------------------------------------------
 -- Criação de um cursor

 DECLARE meuCursor CURSOR
 FOR
    SELECT '001',
           Substring(CODPRO,2,6),
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           0,
           Convert(date,GetDate(),108),
           0,
           0,
           Convert(date,GetDate(),108),
           0,
           0,
           Convert(date,GetDate(),108) 
      FROM SAPIENS_HOM.dbo.E075PRO
     WHERE CodFam in ('010', '015', '020', '025', '030', '040',
                     '042', '050', '052', '055', '060', '070',
                     '071', '075', '076', '077', '078', '100', 
                     '110', '120', '130', '140', '153', '160', 
                     '170', '180', '185', '190', '195', '115', 
                     '105', '199', '106', '125', '135', '136',
                     '191')
OPEN meuCursor 
----------------------------------------------------------------------------
 FETCH NEXT FROM meuCursor
       INTO
       @FIL_CODIGO                    ,
       @PRO_CODIGO                    ,
       @ETQ_ESTOQUEFILIAL             ,
       @ETQ_ESTOQUEFILIALPRODUCAO     , 
       @ETQ_ESTOQUEMINIMO             ,
       @ETQ_ESTOQUEMAXIMO             ,
       @ETQ_ESTOQUECOMPRADO           ,
       @ETQ_VALORULTIMOCUSTO          ,
       @ETQ_VALORCUSTOCOMPRA          ,
       @ETQ_VALORCUSTOLIQUIDO         ,
       @ETQ_VALORCUSTOINVENTARIO      ,
       @ETQ_VALORCUSTOCOMPRAMEDIO     ,
       @ETQ_VALORCUSTOLIQUIDOMEDIO    ,
       @ETQ_VALORCUSTOINVENTARIOMEDIO ,
       @ETQ_DATAULTIMAVENDA           ,
       @ETQ_VALORULTIMAVENDA          ,
       @ETQ_QUANTIDADEVENDIDA         ,
       @ETQ_DATAULTIMACOMPRA          ,
       @ETQ_VALORULTIMACOMPRA         ,
       @ETQ_QUANTIDADECOMPRADA        ,
       @ETQ_DATAALTERACAO             ;   

WHILE (@@FETCH_STATUS = 0)
BEGIN
      SET @ETQ_ID = 
          (SELECT ISNULL(MAX(ETQ_ID),0) + 1 
             FROM NESKI_DBA_ERP..ETQ_00)    

 INSERT INTO neski_dba_erp.dbo.Etq_00
 ( 
        ETQ_ID,
        FIL_CODIGO,
        PRO_CODIGO,
        ETQ_ESTOQUEFILIAL,
        ETQ_ESTOQUEFILIALPRODUCAO,
        ETQ_ESTOQUEMINIMO,
        ETQ_ESTOQUEMAXIMO,
        ETQ_ESTOQUECOMPRADO,
        ETQ_VALORULTIMOCUSTO,
        ETQ_VALORCUSTOCOMPRA,
        ETQ_VALORCUSTOLIQUIDO,
        ETQ_VALORCUSTOINVENTARIO,
        ETQ_VALORCUSTOCOMPRAMEDIO,
        ETQ_VALORCUSTOLIQUIDOMEDIO,
        ETQ_VALORCUSTOINVENTARIOMEDIO,
        ETQ_DATAULTIMAVENDA,
        ETQ_VALORULTIMAVENDA,
        ETQ_QUANTIDADEVENDIDA,
        ETQ_DATAULTIMACOMPRA,
        ETQ_VALORULTIMACOMPRA,
        ETQ_QUANTIDADECOMPRADA,
        ETQ_DATAALTERACAO
 )
 VALUES
(
        @ETQ_ID                        ,
        @FIL_CODIGO                    ,
        @PRO_CODIGO                    ,
        @ETQ_ESTOQUEFILIAL             ,
        @ETQ_ESTOQUEFILIALPRODUCAO     , 
        @ETQ_ESTOQUEMINIMO             ,
        @ETQ_ESTOQUEMAXIMO             ,
        @ETQ_ESTOQUECOMPRADO           ,
        @ETQ_VALORULTIMOCUSTO          ,
        @ETQ_VALORCUSTOCOMPRA          ,
        @ETQ_VALORCUSTOLIQUIDO         ,
        @ETQ_VALORCUSTOINVENTARIO      ,
        @ETQ_VALORCUSTOCOMPRAMEDIO     ,
        @ETQ_VALORCUSTOLIQUIDOMEDIO    ,
        @ETQ_VALORCUSTOINVENTARIOMEDIO ,
        @ETQ_DATAULTIMAVENDA           ,
        @ETQ_VALORULTIMAVENDA          ,
        @ETQ_QUANTIDADEVENDIDA         ,
        @ETQ_DATAULTIMACOMPRA          ,
        @ETQ_VALORULTIMACOMPRA         ,
        @ETQ_QUANTIDADECOMPRADA        ,
        @ETQ_DATAALTERACAO                  
)
END

CLOSE meuCursor
DEALLOCATE meuCursor

SOLUTION

declare @Hoje datetime;
set @Hoje= cast(current_timestamp as date);

declare @Ult_ID int;
set @Ult_ID= coalesce ((SELECT max(ETQ_ID) from neski_dba_erp.dbo.Etq_00), 0);

INSERT INTO neski_dba_erp.dbo.Etq_00 with (tablock)
 (      
    ETQ_ID,
    FIL_CODIGO,
    PRO_CODIGO,
    ETQ_ESTOQUEFILIAL,
    ETQ_ESTOQUEFILIALPRODUCAO,
    ETQ_ESTOQUEMINIMO,
    ETQ_ESTOQUEMAXIMO,
    ETQ_ESTOQUECOMPRADO,
    ETQ_VALORULTIMOCUSTO,
    ETQ_VALORCUSTOCOMPRA,
    ETQ_VALORCUSTOLIQUIDO,
    ETQ_VALORCUSTOINVENTARIO,
    ETQ_VALORCUSTOCOMPRAMEDIO,
    ETQ_VALORCUSTOLIQUIDOMEDIO,
    ETQ_VALORCUSTOINVENTARIOMEDIO,
    ETQ_DATAULTIMAVENDA,
    ETQ_VALORULTIMAVENDA,
    ETQ_QUANTIDADEVENDIDA,
    ETQ_DATAULTIMACOMPRA,
    ETQ_VALORULTIMACOMPRA,
    ETQ_QUANTIDADECOMPRADA,
    ETQ_DATAALTERACAO
)
    SELECT @Ult_ID + row_number() over(order by (SELECT 0)), 
           '001', 
           substring (CODPRO,2,6), 
           0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
           @Hoje, 0, 0, @Hoje, 0, 0, @Hoje
      from SAPIENS_HOM.dbo.E075PRO
     where CodFam in ('010', '015', '020', '025', '030', '040',
                     '042', '050', '052', '055', '060', '070',
                     '071', '075', '076', '077', '078', '100', 
                     '110', '120', '130', '140', '153', '160', 
                     '170', '180', '185', '190', '195', '115', 
                     '105', '199', '106', '125', '135', '136',
                     '191')
      AND CODPRO NOT IN ('3200380','0300310','0400330');
sql sql-server sql-insert cursor

  • If you put the FETCH at the end of the cycle WHILE doesn’t work?

  • 1

    change your ETQ_ID to Identity remove from Insert and be happy.

  • @Marconciliosouza no more!

  • I can’t do this @Marconciliosouza.

  • I’ll try @Joãomartins, I’ll let you know if it worked.

  • How the columns are declared ETQ_DATAULTIMAVENDA, ETQ_DATAULTIMACOMPRA and ETQ_DATAALTERACAO?

Show 1 more comment

1 answer

3

I think using cursor for something so simple is complicating something that can be solved with INSERT ... SELECT.

-- código #1
declare @Hoje datetime;
set @Hoje= cast(current_timestamp as date);

declare @Ult_ID int;
set @Ult_ID= coalesce ((SELECT max(ETQ_ID) from neski_dba_erp.dbo.Etq_00), 0);

INSERT INTO neski_dba_erp.dbo.Etq_00 with (tablock)
 (      ETQ_ID,
        FIL_CODIGO,
        PRO_CODIGO,
        ETQ_ESTOQUEFILIAL,
        ETQ_ESTOQUEFILIALPRODUCAO,
        ETQ_ESTOQUEMINIMO,
        ETQ_ESTOQUEMAXIMO,
        ETQ_ESTOQUECOMPRADO,
        ETQ_VALORULTIMOCUSTO,
        ETQ_VALORCUSTOCOMPRA,
        ETQ_VALORCUSTOLIQUIDO,
        ETQ_VALORCUSTOINVENTARIO,
        ETQ_VALORCUSTOCOMPRAMEDIO,
        ETQ_VALORCUSTOLIQUIDOMEDIO,
        ETQ_VALORCUSTOINVENTARIOMEDIO,
        ETQ_DATAULTIMAVENDA,
        ETQ_VALORULTIMAVENDA,
        ETQ_QUANTIDADEVENDIDA,
        ETQ_DATAULTIMACOMPRA,
        ETQ_VALORULTIMACOMPRA,
        ETQ_QUANTIDADECOMPRADA,
        ETQ_DATAALTERACAO
 )
SELECT @Ult_ID + row_number() over(order by (SELECT 0)), 
       '001', 
        substring (CODPRO,2,6), 
        0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
        @Hoje, 0, 0, @Hoje, 0, 0, @Hoje
  from SAPIENS_HOM.dbo.E075PRO
  where CodFam in ('010', '015', '020', '025', '030', '040',
                   '042', '050', '052', '055', '060', '070',
                   '071', '075', '076', '077', '078', '100', 
                   '110', '120', '130', '140', '153', '160', 
                   '170', '180', '185', '190', '195', '115', 
                   '105', '199', '106', '125', '135', '136',
                   '191');

I suggest reading the article "Bulk data import", which is in the Articles section of SQL Port.

  • It worked super well here, thank you very much! :-)

  • @Eduardoteixeira It is much simpler with INSERT ... SELECT. And faster!

Browser other questions tagged sql sql-server sql-insert cursor

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