At my company old sales orders are deleted from the source system after 2 years because they are no longer needed there.
To keep the data available for reporting, I've created a pipeline that will automatically extract the sales order information and store it in a lakehouse table at the end of the year.
So I currently have a lakehouse with tables: Sales Order History 2018-2023.
For reporting I have 1 generic Sales Order History view that combines data from all these tables.
Because I'm lazy and don't want to update the view manually every year, I have made a stored procedure that will alter the view's select statement
(something like this)
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STRING_AGG('SELECT * FROM ' + QUOTENAME(TABLE_NAME), ' UNION ALL ')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'Sales Order History%';
SET @sql = 'CREATE OR ALTER VIEW AllHistory AS ' + @sql;
EXEC sp_executesql @sql;
Whenever I run the stored procedure, I don't get any errors, but the view is not updated either.
Am I doing something wrong? Is this a fabric bug?
