daily pastebin goal

ComposableDML cannot insert into a table with a FOREIGN KEY

jamiet Aug 10th, 2012 140 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*This code demonstrates the fact that you can't use composable DML to insert into a table that has a FK*/
  2. USE tempdb
  3. CREATE TABLE [TableWithPK] ([col1] INT PRIMARY KEY);
  4. CREATE TABLE [TableWithFK] ([col1] INT REFERENCES [TableWithPK]([col1]));
  5. /*Following statement fails with error:
  6. Msg 356, Level 16, State 1, Line 17
  7. The target table 'TableWithFK' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the
  8. FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'FK__TableWithF__col1__2D27B809'.
  9. */
  10. INSERT  [TableWithFK]([col1])
  11. SELECT  [col1]
  12. FROM    (
  13.                 INSERT  [TableWithPK]([col1])
  14.                 OUTPUT  [INSERTED].[col1]
  15.                 VALUES(1)
  16.                 )insert_out
  17. ;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand