Thanks for the replies guys. I have been swamped at work and had not had a chance, until now, to really look at your responses.
I haven't actually designed the database, but wanted to test out a few things before I did. Forget this example for a second, are you saying that you'll usually not have to INSERT multiple primary keys from other tables into a table? I see myself needing this a lot in the finished design.
I don't see the need for the first two tables.
Sorry man. As I said I was at work and really couldn't test it. I've got some time and tested it. I ran both sub queries individually and they both worked fine. When I run the full query I receive the following error in SSMS 2005 Express.
best way to find out is to test it
So SQL Server is not liking the combination of the 2 queries. They both run fine independently but when run together I receive the error.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '`'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.