Pastebin is 300% more awesome when you are logged in. Sign Up, it's FREE!
Guest

ComposableDML cannot insert into a table with a FOREIGN KEY

By: jamiet on Aug 10th, 2012  |  syntax: T-SQL  |  size: 0.73 KB  |  hits: 111  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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. ;