To insert data with select from a table that has null values in some fields into a table that does not allow null values, you must provide a substitute value for any NULL entries in the original table.
For example, to insert data into an advances table that does not allow null values, this example substitutes “0” for the NULL fields:
insert advances select pub_id, isnull(advance, 0) from titles
Without the isnull function, this command inserts all the rows with non-null values into advances and produces error messages for all the rows where the advance column in titles contains NULL.
If you cannot make this kind of substitution for your data, you cannot insert data containing null values into columns with a NOT NULL specification.