In theory the above INSERT statement should throw a foreign constraint error because when it inserts the very first record (Paul, Alice) there is no record with 'Alice' primary key.

In theory the above INSERT statement should throw a foreign constraint error...

I thought so, too.  I guess this is an interesting application of the UNION ALL statement.  It seems that all the statements are handled in a transaction, then the foreign key constraint is checked.  If you run separate INSERT statements, then the anticipated foreign constraint error does occur.  This will have to be filed away in the grey matter as an interesting trick or gadget when needed for a future application.

Hi Al,

>>In theory the above INSERT statement should throw a foreign constraint error because when it inserts the very first record (Paul, Alice) there is no record with 'Alice' primary key.<<

This is incorrect. There is only one INSERT statement, not a series of INSERT statements. It inserts the result of a query, that just happpens to be a bunch of UNION'ed together SELECT's for constants. However, there is no theoretic difference between this INSERT and something as

  INSERT INTO Tab1 (col1, col2)
  SELECT col3, col4
  FROM Tab2
  WHERE something = something_else;

In other words - the whole set of rows returned by the SELECT statement is inserted at once, not row by row.

Hi,

I added ## to the table names to make tables temporary and it returned the following:

1. The message that it skips Foreign Key constraint that is not allowed on temp tables.

2. 4 rows affected. Which is expected message for the Insert statement

3. Msg 4701, Level 16, State 1, Line 1

Cannot find the object "emp_mgr" because it does not exist or you do not have permissions.

This is expected too because of the dbo prefix, discussed by other posters here.