Insert Multiple with Subquery

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])
VALUES
([SELECT ProductId FROM Products], ["~repository/Chair_Arms.jpg"], [5], ["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.
LVL 14
DesignbyonyxAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, because in sql server, you should use ' and not " for strings:
INSERT INTO ImageMap 
([ProductId], [ImageFile], [SortOrder], [CreatedBy], [ModifiedBy])
SELECT ProductId, '~repository/Chair_Arms.jpg', 5, 'Admin', 'Admin' 
  FROM Products

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no VALUES:
0
 
DesignbyonyxAuthor Commented:
Thanks for the quick response.  Your solution was my actually what I did as my first attempt at this, and I got what I expected :

Invalid Column Name "~repository/Chair_Arms.jpg"
Invalid Column Name "Admin"
Invalid Column Name "Admin"

The syntax accepts numbers as "Static Insert Values" (as it were) but does not like strings- thinks they are column names (rightfully so).
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
obareeyCommented:
you can do that only if "[SELECT ProductId FROM Products]" query returns 1 row. also, what is the error for your query...?
0
 
DesignbyonyxAuthor Commented:
I am marking angelIII's answer as the accepted solution (even though I had tried that method verbatim prior to my initial post).  But for some reason while I was looking at his post the solution came to me... I think it was the green highlighting of the quoted text:  

It is important to note that if you are in the Query Pane of SQL Server Management Studio, the quotes must be SINGLE QUOTES - not DOUBLE!!!  Thanks again for the quick response.
INSERT INTO ImageMap 
([ProductId], [ImageFile], [SortOrder], [CreatedBy], [ModifiedBy])
SELECT ProductId, '~repository/Chair_Arms.jpg', 5, 'Admin', 'Admin' FROM Products

Open in new window

0
 
DesignbyonyxAuthor Commented:
Just acknowledging angelIII- you posted the fully correct solution while I was adding my last comment.  Thanks a bunch!  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.