Writing to different tables from writable view

Question

I want to write to different tables depending on the state of my development (b=backup, t=testing).

The minimal example showing the problem is the following:

SELECT s.Monat FROM (
SELECT 'b' as mode, * FROM [BACKUP].IstMonat
UNION 
SELECT 't' as mode, * FROM [TESTING].IstMonat
) s
where s.mode = 't' -- in the real world discovered by a sub query.

How can i create such a view that is actually writable?

Currently ...

INSERT INTO TestView(Monat) VALUES (1);

... results in ...

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'TestView' failed because it contains a derived or constant field.

score 2 · Answer 1 · answered Apr 28 '13 at 14:19

I solved it with something like the following:

CREATE TRIGGER ModifyAllSynonyms ON [IMPORT].ImpIstMonat
INSTEAD OF INSERT
AS
IF ( EXISTS (SELECT * FROM dbo.ModeSwitch WHERE mode = 'backup') )
INSERT INTO [BACKUP].IstMonat 
SELECT * FROM inserted
ELSE
INSERT INTO [TESTING].IstMonat
SELECT * FROM inserted

score 1 · Accepted Answer · answered Apr 26 '13 at 15:57

A (non-indexed) View cannot have two potentially writable table-targets at run-time. That's one of the reasons that Views with UNION in them are automatically read-only.

I can think of only two ways that this could be done:

View Triggers: Create a View with an INSTEAD OF Trigger that procedureally decides which table to write to, (I cannot remember if this has to be an Indexed View or not), or ...
Synonyms: Define your View's target through the use of a Synonym. Then when you want to switch it, simply redefine the synonym.

Writing to different tables from writable view

2 Answers2