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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.