Dev,

When you re-org the query like that it is much more readable. Can we momentarily just focus on this insert query? The following query should insert into employees1 table values for the only two 'not null' columns (besides the identity column) in the table:

INSERT INTO EMPLOYEES1 (LASTNAME, FIRSTNAME)

SELECT LASTNAME, FIRSTNAME

FROM

(SELECT EMPLOYEEID, LASTNAME, FIRSTNAME

FROM EMPLOYEES

WHERE EMPLOYEEID < 5

) ZXTABX1

It generates following error:

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'EmployeeID', table 'Northwind.dbo.Employees1'; column does not allow nulls. INSERT fails.

The statement has been terminated.

1. True enough the employeeID column is not null, but shouldn't it auto-populate?

2. What is the 'ZXTABX1' delimiter? I tried replacing it with semi-column and I get 'Incorrect syntax near ';'.'

3. why doesn't the insert work?