Insert Multiple with Subquery
Posted on 2008-11-17
I know how to do a multiple insert using a subquery:
INSERT INTO table1 (name, description)
(SELECT Name, Description FROM table2 WHERE Status = 1)
But my current situation is kind of weird. I have a bunch of Chairs that can be configured differently, some can have fixed armrests, adjustable armrests, or no armrests. I have one image that shows all three configurations of armrests. I need to assign this image to every product in the catalog. And since each product can be assigned multiple images, I am using an ImageMap table.
So for every product in the Products table, I need to create a row in the ImageMap table and insert STATIC DATA on Every Row. If that sounds weird, look at the query below to see what I would like to be able to do:
NOTE: This query does NOT work
INSERT INTO ImageMap
([ProductId], [ImageFile], [SortOrder], [CreatedBy], [ModifiedBy])
([SELECT ProductId FROM Products], ["~repository/Chair_Arms.jpg"], , ["Admin"], ["Admin"])
As you can see, every insert is exactly the same except for the ProductId, which is Selected from the Products table. Is this possible? I have to do this multiple times for all of the configurable items on a chair. Thanks in advance.