Hi
I’m having issues with ALTER TABLE SWITCH between a staging table and a target table which is schema-bound to an indexed view.
When I issue the SWITCH statement, e.g
ALTER TABLE dbo.MASTERPrices_Staging switch TO dbo.MASTERPrices;
I get the following message:-
Msg 11402, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Target table ‘MASTERPrices’ is referenced by 1 indexed view(s), but source table ‘MASTERPrices_Staging’ is only referenced by 0 indexed view(s). Every indexed view on the target table must have at least one matching indexed view on the source table.
We use only SQL Server 2008 Standard Edition so partitioning is not a solution.
I need to SWITCH about 10 million rows daily, yet not loose the indexed view solution?
If I alter the view to not schema bound, then SWITCH Works, but when I ALTER the view again and set it back to SCHEMABOUND, all the indexes (12 of them including clustered index have dissapeared)
Please does anybody know a workaround ?