?
Solved

Insert Multiple with Subquery

Posted on 2008-11-17
6
Medium Priority
?
261 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Designbyonyx
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22977479
no VALUES:
0
 
LVL 14

Author Comment

by:Designbyonyx
ID: 22977670
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22977705
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 4

Expert Comment

by:obareey
ID: 22977737
you can do that only if "[SELECT ProductId FROM Products]" query returns 1 row. also, what is the error for your query...?
0
 
LVL 14

Author Comment

by:Designbyonyx
ID: 22977782
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
 
LVL 14

Author Comment

by:Designbyonyx
ID: 22977807
Just acknowledging angelIII- you posted the fully correct solution while I was adding my last comment.  Thanks a bunch!  
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question