SQL Server Error Messages - Msg 330

Error Message

Server: Msg 330, Level 16, State 1, Line 1
The target '<Object Name>' of the OUTPUT INTO clause cannot be a view or common table expression.

Causes

The OUTPUT clause, introduced in SQL Server 2005, returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE or MERGE statement.

These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

One of the restrictions when using the OUTPUT INTO clause is that it cannot be used to insert into a view, common table expression or rowset function; otherwise this error message will be encountered.

To illustrate how this error is generated, given the following tables structures:

CREATE TABLE [dbo].[Product] (
    [ProductID]        INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [ProductName]      VARCHAR(100) NOT NULL,
    [UnitPrice]        MONEY,
    [Quantity]         INT
)

CREATE TABLE [dbo].[ProductPriceHistory] (
    [ProductPriceHistoryID]  INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [ProductID]              INT NOT NULL,
    [OldUnitPrice]           MONEY,
    [NewUnitPrice]           MONEY,
    [ChangeDate]             DATETIME DEFAULT (GETDATE()),
    [ModifiedBy]             VARCHAR(50) DEFAULT (SUSER_NAME())
)
GO

CREATE VIEW [dbo].[ProductPriceHistoryByUser]
AS
SELECT * FROM [dbo].[ProductPriceHistory]
WHERE [ModifiedBy] = SUSER_NAME()
GO

As a sales promotion within a company, the price needs to be decreased by 10% for all products where the current price is greater than or equal to $1,000.00. For each product whose price was decreased, the old price and the new price need to be inserted in the product price history table. For security purposes, each user can only view their own product price history records. As such each user will only have access to the view that limits the records they can see from the product price history table.

In fulfilling this requirement, the following UPDATE statement which includes the OUTPUT INTO clause is executed.

UPDATE [dbo].[Product]
SET [UnitPrice] = [UnitPrice] * 0.9
OUTPUT [inserted].[ProductID], [deleted].[UnitPrice], [inserted].[UnitPrice], GETDATE()
INTO [dbo].[ProductPriceHistoryByUser]
WHERE [UnitPrice] >= 1000

But since the target object, in this case the [dbo].[ProductPriceHistoryByUser] view, is a view and not a table, the following error message is encountered:

Msg 330, Level 16, State 1, Line 1
The target 'dbo.ProductPriceHistoryByUser' of the OUTPUT INTO clause cannot be a view or common table expression.

Solution / Work Around

As mentioned earlier, and as the message suggests, the target object of the OUTPUT INTO clause cannot be a view. To overcome this restriction, one thing that can be done is to insert the updated products together with the old and new prices into a temporary table or a table variable. Then after the UPDATE statement, the updated product information can now be inserted into the product price history view.

The following script can be used to replace the UPDATE statement earlier which inserts all updated products together with their old and new unit price after the UPDATE operation.

DECLARE @ProductPriceHistory TABLE (
    [ProductID]          INT,
    [OldUnitPrice]       MONEY,
    [NewUnitPrice]       MONEY
)

UPDATE [dbo].[Product]
SET [UnitPrice] = [UnitPrice] * 0.9
OUTPUT [inserted].[ProductID], [deleted].[UnitPrice], [inserted].[UnitPrice]
INTO @ProductPriceHistory
WHERE [UnitPrice] >= 1000

INSERT INTO [dbo].[ProductPriceHistoryByUser] ( [ProductID], [OldUnitPrice], [NewUnitPrice] )
SELECT [ProductID], [OldUnitPrice], [NewUnitPrice]
FROM @ProductPriceHistory

The first part of the script is the declaration of a table variable that will contain the product ID of the products updated together with the old unit price and new unit price. This table variable is then used as the target table for in the OUTPUT INTO clause of the UPDATE statement.

Lastly, all updated products that are now in the table variable can now be inserted to the product price history view without generating this error.