Insert the result of a Procedure into a new SQL table

Question

I made the following code but I can’t insert the result into the new query table.Can I help myself? Here is the Sql code:

DECLARE @Resultado TABLE
(
 Tabelas nvarchar(max),
 data_sincronização date,
 rodovia nvarchar(max),
 elemento nvarchar(max),
 n_foto int
)

DECLARE @Table_cursor CURSOR 
DECLARE @Data_cursor CURSOR
DECLARE @database_name nvarchar(MAX)
DECLARE @table_name nvarchar(MAX)
DECLARE @n_picture nvarchar(MAX)
DECLARE @data int
DECLARE @rodovia nvarchar(MAX)
DECLARE @elemento nvarchar(MAX)


DECLARE @sql_insert nvarchar(MAX)

SET @Table_cursor = CURSOR FORWARD_ONLY FOR
SELECT T.TABLE_NAME FROM p06617.INFORMATION_SCHEMA.TABLES T
 WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%ATTACH' 
 ORDER BY TABLE_NAME

OPEN @Table_cursor
FETCH NEXT FROM @Table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN

SET @Data_cursor = CURSOR FORWARD_ONLY FOR
SELECT  count(*) as n_foto,FORMAT (@data, 'dd-MM-yyyy')  FROM p06617.sys.sysindexes


OPEN @Data_cursor
FETCH NEXT FROM @Data_cursor INTO @n_picture,@data
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT(@n_picture)
--PRINT N'ESPAÇO'
--print(@table_name)
--SET @sql_insert = 'INSERT INTO Consulta_Nova (Banco, Tabela, Elemento, n_foto, data_sincrozicao) SELECT ''' + @database_name + ''',  + @table_name + ''',  ''' + @elemento + ''', count(*) as n_foto, FORMAT (GDB_FROM_DATE, ''dd-MM-yyyy'') FROM ' + @database_name + '.dbo.' + @table_name + ' GROUP BY FORMAT(GDB_FROM_DATE, ''dd-MM-yyyy'');'
    EXECUTE sp_executesql @sql_insert
INSERT INTO @Resultado (Tabelas,n_foto,elemento) VALUES (@table_name,@n_picture,@data)
FETCH NEXT FROM @Table_cursor INTO @table_name
END
CLOSE @Data_cursor;
DEALLOCATE @Data_cursor;

END
CLOSE @Table_cursor;
DEALLOCATE @Table_cursor;

select * from @Resultado

Answer 1

You just need to put the INSERT before the EXECUTE:

...
SET @sql_insert = 'SELECT ''' + @database_name + ''',  ''' + @table_name + ''',  ''' + @elemento + ''', count(*) as n_foto, FORMAT (GDB_FROM_DATE, ''dd-MM-yyyy'') FROM ' + @database_name + '.dbo.' + @table_name + ' GROUP BY FORMAT(GDB_FROM_DATE, ''dd-MM-yyyy'');';

INSERT INTO Consulta_Nova (Banco, Tabela, Elemento, n_foto, data_sincrozicao) 
EXECUTE sp_executesql @sql_insert;
...

Other than that, quotes were missing from string of SELECT mounted. I corrected the above code.

Recalling that the SELECT must return exactly as determined in INSERT INTO.