Composable DML cannot insert into a table with a FOREIGN KEY

I think this may relate to the restrictions of OUTPUT INTO clause listed on article below.  It sounds like both the DML and OUTPUT INTO must succeed or both will fail, so maybe they don't support constraints or triggers in the OUTPUT INTO to prevent recursion(?)

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Inserting Data Returned From an OUTPUT Clause Into a Table:

When you are capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement and inserting those results into a target table, keep the following information in mind:

The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.

The following restrictions apply to the target of the outer INSERT statement:

The target cannot be a remote table, view, or common table expression.

The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

Triggers cannot be defined on the target.

The target cannot participate in merge replication or updatable subscriptions for transactional replication.

The following restrictions apply to the nested DML statement:

The target cannot be a remote table or partitioned view.

The source itself cannot contain a <dml_table_source> clause.

The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.

@@ROWCOUNT returns the rows inserted only by the outer INSERT statement.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.

In the <dml_table_source> clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

Josh

but it is working

DROP TABLE #TableWithPK

DROP TABLE #TableWithFK

CREATE TABLE #TableWithPK ([col1] INT PRIMARY KEY)

CREATE TABLE #TableWithFK ([col1] INT REFERENCES #TableWithPK([col1]))

INSERT #TableWithFK([col1])

SELECT [col1]

FROM (

INSERT #TableWithPK([col1])

OUTPUT [INSERTED].[col1]

VALUES(1)

)insert_out

SELECT * FROM #TableWithPK

SELECT * FROM #TableWithFK

GO

but it is working

DROP TABLE #TableWithPK

DROP TABLE #TableWithFK

CREATE TABLE #TableWithPK ([col1] INT PRIMARY KEY)

CREATE TABLE #TableWithFK ([col1] INT REFERENCES #TableWithPK([col1]))

INSERT #TableWithFK([col1])

SELECT [col1]

FROM (

INSERT #TableWithPK([col1])

OUTPUT [INSERTED].[col1]

VALUES(1)

)insert_out

SELECT * FROM #TableWithPK

SELECT * FROM #TableWithFK

GO

You get the same error with this statement:

INSERT [TableWithPK]([col1])

OUTPUT [INSERTED].[col1]

INTO [TableWithFK]

VALUES(1)

I usually output into a table variable or temp table then work with it like so:

BEGIN TRAN

DECLARE @TableWithFK TABLE([col1] INT);

INSERT [TableWithPK]([col1])

OUTPUT [INSERTED].[col1]

INTO  @TableWithFK

VALUES(1)

INSERT INTO [TableWithFK]

SELECT * FROM @TableWithFK

COMMIT;

Josh

You can temporarily disable the constraint.

If you created the table with a named constraint (or know the name):

CREATE TABLE [TableWithFK] ([col1] INT, CONSTRAINT FK_MyFK FOREIGN KEY ([col1])  REFERENCES [TableWithPK]([col1]));

ALTER TABLE [TableWithFK] NOCHECK CONSTRAINT FK_MyFK

GO

INSERT [TableWithFK]([col1])

SELECT [col1]

FROM (

INSERT [TableWithPK]([col1])

OUTPUT [INSERTED].[col1]

VALUES(1)

)insert_out

;

GO

ALTER TABLE [TableWithFK] NOCHECK CONSTRAINT FK_MyFK

I came across this when using a MERGE statement.  One way to get around it was to insert into a temp table that matches the definition of the destination table and then insert the contents of that temp table into the destination.

see here for more info:

https://connect.microsoft.com/SQLServer/feedback/details/435031

http://schottsql.blogspot.com/2011/01/sql-2008-merge-and-pkfk-constraints.html

I meant that last ALTER TABLE to say CHECK rather than NOCHECK.

I once solved it by using dynamic SQL. It works but I can't recommend it due to readability is severely poor.

https://sites.google.com/site/mrmbookmarks/msg/sql-inserttoachildtableusingvaluesfromtheoutputclause