Here is my statement:

DELETE FROM table1 OUTPUT deleted.col1, deleted.col2 INTO view1;

It gives me this error:

The target 'view1' of the OUTPUT INTO clause cannot be a view or common table expression.

The view is simple and updatable. Why can I not insert into an updatable view using the OUTPUT clause?

1 Answer 1


From MSDN - Output clause:

"The OUTPUT clause is not supported in the following statements:

  • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
  • INSERT statements that contain an EXECUTE statement.
  • Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
  • The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
  • A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target. "
  • 1
    Shame. I'm just wondering what reasons MS give for this restriction...
    – tuseau
    Commented May 17, 2011 at 15:31
  • You should be able to fix this if you output in a table variable and then you will be able to use this data for your view.
    – Marian
    Commented May 17, 2011 at 17:24
  • It's not a major problem, I can do the same operation in two statements, but the advantage to the output clause is the speed because it's all done in one statement. Table variables are slow!
    – tuseau
    Commented May 18, 2011 at 8:26
  • I think that MS were a little lazy as the view could be a view onto the table you are deleting from (a little recursive I know)... Have you tried outputting to the underlying table? Commented May 18, 2011 at 12:47
  • 1
    OUTPUT INTO has some limitations, including the output to a table with foreign keys, which is not accepted, from what I recall. It's essentially a help when you want to have the last set of data that you've changed and have it in a temp table/table variable and ready to work it further. It's not exactly a statement that does it all in one :).
    – Marian
    Commented May 18, 2011 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.