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
No comments:
Post a Comment