1
I have a query that lists all databases, tables, highway, and dates. only that in the query it returns me an error in the conclusion pq within the field to which are stored all tables, there are tables that do not have a specific column. I am wanting to create an exception so that if you do not have such a column within an 'x' table, it runs without returning an error at the conclusion of the query.
PS* THE RESULT OF THE QUERY APPEARS NORMALLY, BUT IT COMES WITH "Query completed with errors". I’m needing why later I’ll have to automate the query if it wasn’t for the automation I’m thinking of doing, I could leave well so,pq the result appears normally to me.
IN CASE MY CONSULTATION IS THAT:
DECLARE @Resultado TABLE
(
Tabelas nvarchar(max),
data_sincronizacao date,
rodovia nvarchar(max),
elemento nvarchar(max),
n_foto int
)
/*DECLARANDO VARIAVEIS PARA MINHA PROCEDURE */
DECLARE @Table_cursor CURSOR
DECLARE @rodovia nvarchar(MAX)
DECLARE @elemento nvarchar(MAX)
DECLARE @table_name nvarchar(MAX)
DECLARE @database_name nvarchar(MAX)
DECLARE @sql_insert nvarchar(MAX)
/*CHAMANDO MEU CURSOR,APONTADANDO ELE PERCORER A TABELA DE REFERENCIA A ONDE ESTÁ ARMAZENADO OS BANCOS E AS TEBALES COM TERMINO "ATTACH" */
SET @Table_cursor = CURSOR FORWARD_ONLY FOR
SELECT distinct Banco, Tabela, Rodovia, Elemento from Dados.dbo.Referencia
/*LIMPANDO A TABELA "CONSULTA" PARA EVITAR DUPLICAÇÃO */
DELETE FROM Dados.dbo.Consulta
/*ABRINDO O CURSOR PARA PERCORER TODOS OS DADOS DA TABELA REFERENCIA TRAZENDO DADOS DAS RESPECTIVAS COLUNAS */
OPEN @Table_cursor
FETCH NEXT FROM @Table_cursor INTO @database_name, @table_name, @rodovia, @elemento
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Inserindo dados do banco ' + @database_name + ' e tabela ' + @table_name
SET @sql_insert = CONCAT('INSERT INTO Consulta (Banco, Tabela, Rodovia, Elemento, n_foto, data_sincronizacao) SELECT ''', @database_name, ''', ''', @table_name, ''', ''', @rodovia, ''', ''', @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
FETCH NEXT FROM @Table_cursor INTO @database_name, @table_name, @rodovia, @elemento
END
CLOSE @Table_cursor;
DEALLOCATE @Table_cursor;