Suppose we have created
a table tblOrder and inserted some data into it in sql server
CREATE TABLE tblOrder(
OrderId INT
PRIMARY KEY,
OrderName VARCHAR(100)
)
INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')
Now we are creating
other table tblOrderHistory with check conrstaint on column OrderName:
CREATE TABLE tblOrderHistory(
OrderId INT,
OrderName VARCHAR(100) CHECK(LEN(OrderName) > 2),
HistoryDate DATETIME
DEFAULT(GETDATE())
)
If we will insert records into the tblOrderHistory by
executing following sql query:
DELETE FROM tblOrder
OUTPUT deleted.OrderId,DELETED.OrderName
INTO
tblOrderHistory(OrderId,OrderName)
We will get error
message like:
The target table '' of the OUTPUT INTO clause cannot have any
enabled check constraints or any enabled rules. Found check constraint or rule
''.
Cause: It is due to
check constraint on table tblOrderHistory. OUTPUT INTO clause doesn't
insert records in table with check constraint.
Solution:
1. Drop the check constraint on table tblOrderHistory
2. In OUTPUT into clause
first insert into temp table or table variable then insert the data of temp
table or table variable into the tblOrderHistory
No comments:
Post a Comment