Insert the result of a Procedure into a new SQL table

1

I made the following code but I can not insert the result into the New Query table. Can anyone help me? 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
    
asked by anonymous 05.07.2017 / 19:56

1 answer

2

You only need to put INSERT before 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;
...

Otherwise, there were missing quotes in string of SELECT mounted. I fixed it in the above code.

Remembering that SELECT should return exactly as determined in INSERT INTO .

    
05.07.2017 / 21:28