I created a view and it fetches information from an X table. However, this X table must be created in a select, before running the view, so that the data is updated.
How can I create something in the sql server so that as soon as the user clicks on the view, sql executes this script before running the view?
I need to execute this drop, select and update first, before the last select is a view:
DROP TABLE dbo.tblTemporaria;
SELECT
CONVERT(VARCHAR(10), dbo.compra.data_entrada, 103) AS [Data Entrada CTE],
dbo.compra.codigo AS [Código Sistema],
SUBSTRING((CONVERT(VARCHAR(MAX), xml_conhecimento)), (PATINDEX('%<infNFe><chave>%', CONVERT(VARCHAR(MAX), xml_conhecimento)) + 43), 6) AS [Nº Nota Fiscal],
dbo.compra.numero_compra AS [Nº Conhecimento],
Transp.nome AS Transportadora,
dbo.cliente_fornecedor.nome AS Cliente,
SUBSTRING(ClassCliente.nome, 3, 50) AS Classificacao,
dbo.cliente_fornecedor.uf_sigla AS UF,
dbo.compra.cfop_codigo AS CFOP,
dbo.compra.valor_total AS [Vlr. Total Frete],
dbo.compra.observacao AS Observações INTO tblTemporaria
FROM dbo.compra
INNER JOIN dbo.cliente_fornecedor
ON dbo.cliente_fornecedor.codigo = dbo.compra.clifor_codigo
LEFT OUTER JOIN dbo.cliente_fornecedor AS Transp
ON Transp.codigo = dbo.compra.tran_codigo
INNER JOIN dbo.classificacao_cliente AS ClassCliente
ON dbo.cliente_fornecedor.clascli_codigo_1 = ClassCliente.codigo
WHERE (dbo.compra.data_entrada >= '01/01/2015')
AND (dbo.compra.cfop_codigo IN ('2353', '1353'));
UPDATE tblTemporaria
SET [Nº Nota Fiscal] = NULL
WHERE [Código Sistema] IN (SELECT
[Código Sistema]
FROM tblTemporaria
WHERE ISNUMERIC([Nº Nota Fiscal]) = 0);
View
SELECT
r.Cliente,
r.Transportadora,
r.[Data Entrada CTE],
r.[Nº Conhecimento],
r.[Vlr. Total Frete],
r.CFOP,
r.Observações,
r.[Nº Nota Fiscal],
CONVERT(VARCHAR(10), NotaFiscal.data, 103) AS [Data Emissão NF],
NotaFiscal.peso_liquido_volume AS [Peso Líquido],
NotaFiscal.peso_bruto_volume AS [Peso Bruto],
NotaFiscal.valor_total_produtos AS [Valor Total Produtos],
NotaFiscal.valor_total AS [Valor Total Nota]
FROM dbo.tblTemporaria AS r
LEFT OUTER JOIN dbo.nota_fiscal_venda AS NotaFiscal
ON NotaFiscal.numero_nota = r.[Nº Nota Fiscal]