1. True enough the employeeID column is not null, but shouldn't it auto-populate?
If it's identity column, then yes it should be auto populated. Please crosscheck on Identity column for Employee1 table. If possible post DDL here.
2. What is the 'ZXTABX1' delimiter? I tried replacing it with semi-column and I get 'Incorrect syntax near ';'.'
It's just an alias for sub query (derived table).
3. why doesn't the insert work?
Let me see the DDL.