Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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. "
share|improve this answer
1  
Shame. I'm just wondering what reasons MS give for this restriction... –  tuseau May 17 '11 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 May 17 '11 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 May 18 '11 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? –  Andrew Bickerton May 18 '11 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 May 18 '11 at 14:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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