Apr 4, 2013

The target '' of the INSERT statement cannot be a view or common table expression when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.


Suppose we have created Employee table in current database. Now we are creating other table tblData:

CREATE TABLE tbldata(
    Data VARCHAR(100),
    IsInteger BIT
)

Creating a view:

CREATE VIEW vwData
AS SELECT Data FROM tbldata

If we will execute following sql query:

INSERT INTO vwData
SELECT  Name
FROM(
    DELETE TOP(1)
    FROM Employee
    OUTPUT DELETED.Name
) Temp

Or

WITH cteData
AS(
    SELECT Data FROM tbldata
)
INSERT INTO cteData
SELECT  Name
FROM(
    DELETE TOP(1)
    FROM Employee
    OUTPUT DELETED.Name
) Temp

We will get error message :

The target '' of the INSERT statement cannot be a view or common table expression when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Cause: We cannot insert records into CTE (Common table expression) or view from nested from clause.  So we have to insert any table.

Solution:

Instead of CTE or view we are directly inserting into the table tblData

INSERT INTO tblData
SELECT  Name
FROM(
    DELETE TOP(1)
    FROM Employee
    OUTPUT DELETED.Name
) Temp

No comments:

Post a Comment