[thelist] SQL: Insert with Sub Queries

[Casey Crookston]

More info:

We are listing cars for sale at an auto dealership.  It used to be that
the relationship of autos to photos was one to one, thus, the single
auto photo was stored in the Vehicles table.

Now, they want the ability to post multiple images per auto.  So, we
have created a new table called Photos with the columns: vehicleID,
photo, isMain, and sortOrder.

We need to populate the initial data in the Photos table with the
current one-to-one data in the Vehicles table, and set the existing
photo to the main photo.  Maybe now this query will make a little more
sense:

INSERT INTO photos (vehicleID, photo, isMain, sortOrder) VALUES
((SELECT vehicleID FROM Vehicles), (SELECT vehicleimage FROM  Vehicles),
'Yes', 1)

In Query Analyzer I'm being told:

Server: Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are
allowed.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

Thanks,

Casey