在select insert中的SQL Server子查询

Any suggestions on getting this to work? syntactically it is correct, and the individual sub-queries return a single result, however when combined the sub-queries do not return results to the insert statement. Essentially i am trying to insert these records into the table if they do not exist in the view.Any suggestions on getting this to work? syntac