Aug 7, 2013

The target table '' of the OUTPUT INTO clause cannot have any enabled check constraints or any enabled rules. Found check constraint or rule ''.


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