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