A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.

Suppose we have created two tables tblOrder and tblOrderHistory and inserted some data into it by executing following sql queries:

CREATE TABLE tblOrder(

    OrderId INT PRIMARY KEY,

    OrderName VARCHAR(100)

)

INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')

CREATE TABLE tblOrderHistory(

    OrderId INT PRIMARY KEY,

    OrderName VARCHAR(100),

    HistoryDate DATETIME DEFAULT(GETDATE())

)

Now if we will execute following sql query:

SELECT * FROM(

DELETE FROM tblOrder

OUTPUT deleted.OrderId,DELETED.OrderName ) AS tbl

We will get error message :

A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.

Cause: If we are using nested INSERT, UPDATE, DELETE, or MERGE statement it is necessary to insert the output into the some other table.

Solution:

Insert the output into the tblOrderHistory:

INSERT INTO tblOrder(OrderId,OrderName)

SELECT * FROM(

DELETE FROM tblOrder

OUTPUT deleted.OrderId,DELETED.OrderName ) AS tbl