How to take all values of a select and run in a Procedure?

Question

How to take all the values of a select and play in a precedent to give an Insert without running one value at a time?

I have the following sql code:

   --drop table #tabelaEntSaida;

   --drop table #tabelaEntSaida;

 CREATE TABLE #tabelaEntSaida (
  EmpCod varchar (30),
  FiscalNFDataEmissao datetime,
  FiscalNFDataSaiEnt datetime,
  FiscalNFEspec varchar(30), 
  NatOperacao varchar(30),
  FiscalNFSerie varchar(5), 
  FiscalNFnum varchar(50), 
  PaisSigla varchar(3), 
  UfSigla varchar(2), 
  EntCod varchar(30), 
  UsuCod varchar(30)
)

DECLARE 
@EmpCod varchar(30), 
@FiscalNFDataEmissao datetime,
@FiscalNFDataSaiEnt datetime,
@FiscalNFEspec varchar(30), 
@NatOperacao varchar(50),
@FiscalNFSerie varchar(5), 
@FiscalNFnum varchar(50), 
@PaisSigla varchar(3), 
@UfSigla varchar(2), 
@EntCod varchar(30), 
@USUARIO varchar(30),
@MSG varchar(50),
@Contador varchar(30)

set @MSG = ''


 DECLARE cursor_gera_entrada_saida CURSOR FOR

 SELECT DISTINCT
    NF.EmpCod, 
    NF.FiscalNFDataEmis,
    NF.FiscalNFDataSaiEnt, 
    NF.FiscalNFEspec, 
    ITNF.NatOpCodEstr,
    NF.FiscalNFSerie, 
    NF.FiscalNFnum, 
    NF.PaisSigla, 
    NF.UfSigla, 
    NF.EntCod,
    NF.UsuCod
FROM 
FISCAL_NF NF
LEFT JOIN FISCAL_ITEM_NF ITNF ON ITNF.FiscalNFChv = NF.FiscalNFChv
WHERE NF.FiscalNFEspec = 'NFC-E' and NF.EmpCod = '01.01'


 OPEN cursor_gera_entrada_saida


 FETCH NEXT FROM cursor_gera_entrada_saida INTO 

@EmpCod, 
@FiscalNFDataEmissao,
@FiscalNFDataSaiEnt,
@FiscalNFEspec, 
@NatOperacao,
@FiscalNFSerie, 
@FiscalNFNum, 
@PaisSigla, 
@UfSigla, 
@EntCod, 
@USUARIO


WHILE @@FETCH_STATUS = 0
BEGIN

/* inserindo na tabela temporaria */
INSERT INTO #tabelaEntSaida (
  EmpCod,
  FiscalNFDataEmissao,
  FiscalNFDataSaiEnt,
  FiscalNFEspec, 
  NatOperacao,
  FiscalNFSerie, 
  FiscalNFnum, 
  PaisSigla, 
  UfSigla, 
  EntCod, 
  UsuCod
) values (
    @EmpCod, 
    @FiscalNFDataEmissao, 
    @FiscalNFDataSaiEnt,
    @FiscalNFEspec, 
    @NatOperacao,
    @FiscalNFSerie, 
    @FiscalNFNum, 
    @PaisSigla, 
    @UfSigla, 
    @EntCod, 
    @USUARIO
)

/* insere na proceure */
EXECUTE GERA_LIVRO_ENT_bkp 
    @EmpCod,
    @FiscalNFDataEmissao,
    @FiscalNFDataSaiEnt,
    @NatOperacao,
    @FiscalNFEspec,
    @FiscalNFSerie,
    @FiscalNFNum,
    @FiscalNFNum,
    @UfSigla,
    @PaisSigla,
    @EntCod,
    '',
    @USUARIO,
    @MSG

-- Lendo a próxima linha
FETCH NEXT FROM cursor_gera_entrada_saida INTO 
    @EmpCod, 
    @FiscalNFDataEmissao,
    @FiscalNFDataSaiEnt,
    @FiscalNFEspec, 
    @NatOperacao,
    @FiscalNFSerie, 
    @FiscalNFnum, 
    @PaisSigla, 
    @UfSigla, 
    @EntCod, 
    @USUARIO
  END

 select * from #tabelaEntSaida;

 drop table #tabelaEntSaida;

 -- Fechando Cursor para leitura
CLOSE cursor_gera_entrada_saida

-- Desalocando o cursor
DEALLOCATE cursor_gera_entrada_saida

Image of the returned select. I want you to play all these values in the past:

Answer 1

Until a while ago I sent several records to a trial as follows: converting the result of select for XML, the precedent expected as a parameter a VARCHAR(7999) and was used the OPENXML to read the XML and insert the records into a temporary table. Only I found a more elegant way to do this: using Table-Valued Parameters

Taking your example code, I created the table FISCAL_NF_BKP to enter all incoming records in the process.

1. Set a Type as Table and create the precedent by receiving the type created as parameter.

CREATE TYPE RegistrosNotaFiscal 
      AS TABLE
         (EmpCod VARCHAR (30),
         FiscalNFDataEmissao DATETIME,
         FiscalNFDataSaiEnt DATETIME,
         FiscalNFEspec VARCHAR(30), 
         NatOperacao VARCHAR(30),
         FiscalNFSerie VARCHAR(5), 
         FiscalNFnum VARCHAR(50), 
         PaisSigla VARCHAR(3), 
         UfSigla VARCHAR(2), 
         EntCod VARCHAR(30), 
         UsuCod VARCHAR(30))
   GO

     CREATE PROCEDURE dbo.GERA_LIVRO_ENT_bkp (@EntradaSaida RegistrosNotaFiscal READONLY)

    AS

          SET NOCOUNT ON

         -- CONTEÚDO DA SUA PROCEDURE ...

         INSERT INTO FISCAL_NF_BKP
         SELECT * FROM @EntradaSaida
    GO

2. After that, you can declare a table type variable created previously, insert records and call the past.

    DECLARE @TabelaEntradaSaida AS RegistrosNotaFiscal

    INSERT INTO @TabelaEntradaSaida
    SELECT DISTINCT
        NF.EmpCod, 
        NF.FiscalNFDataEmissao,
        NF.FiscalNFDataSaiEnt, 
        NF.FiscalNFEspec, 
        ITNF.NatOpCodEstr,
        NF.FiscalNFSerie, 
        NF.FiscalNFnum, 
        NF.PaisSigla, 
        NF.UfSigla, 
        NF.EntCod,
        NF.UsuCod 
    FROM FISCAL_NF NF
    LEFT JOIN FISCAL_ITEM_NF ITNF ON ITNF.FiscalNFChv = NF.FiscalNFChv
    WHERE NF.FiscalNFEspec = 'NFC-E' and NF.EmpCod = '01.01'


    SELECT * FROM @TabelaEntradaSaida


    EXEC GERA_LIVRO_ENT_bkp @TabelaEntradaSaida;

If you’re going to do it in C# you can see in this reference.