Unable to insert into FakeTable View that uses multiple tables?

Azhkanizkael

When i faketable a view, I can't seem to be able to insert into the view.

View or function 'dbo.vwName' is not updatable because the modification affects multiple base tables.

is there a way around this that doesn't involve requiring me to insert into all the different tables that go into this view?

Tianjiao_Li

This looks like a good question to tSQLt developers. We have an article https://www.red-gate.com/simple-talk/sql/sql-tools/ten-things-i-wish-id-known-when-i-started-using-tsqlt-and-sql-test/ where mentioned some possible workaround for this issue and hopefully you'll find it helpful:

Note: for more complex views, SQL Server can get a little upset when parsing your test if it deems the view you are faking is non-updatable (this is because at parse time, SQL Server doesn’t recognise the effect that FakeTable will have at runtime). Whilst there is a pair of procedures (SetFakeViewOn and SetFakeViewOff) that according to this blog post bySebastian Meine (one of the authors of tSQLt) you can use when creating your test, to temporarily modify the schema of the database with an insteadof trigger to allow the test to compile. Unfortunately other than that blog post, these are very poorly documented, and so I would suggest that if you encounter issues like this you simply use dynamic SQL to do the necessary data insertions.

Azhkanizkael

Working workaround for me:

After exec'ing a fake table on vwName I moved the Insert into an exec of it's own and works as the bypass.

EXEC('INSERT INTO vwName(columns) VALUES(data)')