How to Insert Values into a Table, Using a Subquery with More Than One Result

How can I insert values into a table, using a subquery with more than one result?

You want:

insert into prices (group, id, price)
select 
    7, articleId, 1.50
from article where name like 'ABC%';

where you just hardcode the constant fields.

How do I insert multiple rows with a subquery as one of the value?

Use INSERT ... SELECT ... without VALUES:

INSERT INTO accounts_account_preferences (account_id, preference_id) 
SELECT account_id, 2 
FROM accounts_account_preferences 
WHERE preference_id = 1

insert multiple rows using subquery

INSERT INTO qualification_lookup (variation, correct_qualification) 
select Qualification,'A-Level' from student   where Qualification like 'A%' 

SQL Insert into table where subquery returns more than 1 value

I should say up front that I don't know the larger context of your problem or how many records you need to insert. But, just based on the code you posted, this looks like it might better meet your needs:

INSERT INTO [dbo].[tbSurveyLocation]
([SurveyUID]
,[LocationParentID]
,[LocationID]
,[Comment]
,[SurveyParentTypeID]
,[CanSurvey]
,[Surveyed]
,[Status]
,[LastUpdate]
,[LastUpdateUser]
,[ClientCode])

SELECT  -- I'm going to keep this subquery because it isn't clear whether tbSurvey 
        -- is related to the rooms and business units in any way.
       (SELECT   Top 1 -- this ensures you only get one record back
                 SurveyUID
        FROM     tbSurvey
        WHERE    SurveyTitle = 'CARLISLE PARK CRICKET PAVILLION'
        -- If it matters which row you get, you can sort the survey with an ORDER BY here.
        )
     , 30
     , tblRoom.RoomID
     , ''
     , 203
     , 1
     , 1
     , 0
     , CONVERT(DATETIME, '10/01/2014 00:00', 102)
     ,'PDS 2014'
     ,'~K2~'
FROM   tbBuilding 
    JOIN tbBuildingLinkBusinessUnit ON tbBuilding.BuildingID = tbBuildingLinkBusinessUnit.BuildingID 
    JOIN tbBusinessUnit ON tbBuildingLinkBusinessUnit.BusinessUnitID = tbBusinessUnit.BusinessUnitID 
    JOIN tbFloor ON tbBuilding.BuildingID = tbFloor.BuildingID 
    JOIN tbRoom ON tbFloor.FloorID = tbRoom.FloorID
 WHERE tbBusinessUnit.BusinessUnitName like '%CARLISLE PARK CRICKET PAVILLION%')

What I just did will give you records for all the rooms that were associated with the business unit "CARLISLE PARK CRICKET PAVILLION". If you need just one room, you could change the SELECT right under the target column list to SELECT TOP 1. (That will give you an arbitrary room that meets the criteria; if you need a particular room, like the one with the lowest ID, use an ORDER BY clause at the end to control what room is the "top 1".)

Now, if you need to do something like link the survey title and the business unit name, so they are the same, and run this for all the surveys/business units, not just Carlisle Park, let me know in the comments. If so, please let me know how many surveys you expect with the same survey title (One? More than one?) and whether the number of rooms you expect for a given business unit is greater than one.

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

INSERT INTO Results (People, names )
   SELECT d.id, 'Henry'
   FROM Names f
   JOIN People d ON d.id  = f.id

Combine the static string Henry with your SELECT query.

How to insert into a table based on a multi-row subquery?

Use insert . . . select:

INSERT INTO Dashboard_Configuration (User_id, name)
    SELECT @user_id, name
    FROM Dashboard_Diagram;

Note that this also lists the columns explicitly. The names may not be correct (you don't specify them in the question), but it is a best practice to list the columns.

EDIT:

There is a type conflict above. Do you really intend this?

INSERT INTO Dashboard_Configuration (User_id, diagram_id)
    SELECT @user_id, dd.diagram_id
    FROM Dashboard_Diagram dd;

Insert Into with subquery to insert multiple rows into database

Something like this should work:

scmd.CommandText = "INSERT INTO notificationAck (Email, 
                                                 Notification, 
                                                 isAcknowledged) 
                    SELECT Email, '" + notification + "', 0
                    FROM AspNetUsers";

INSERT INTO SELECT does not require VALUES. Hence, you only have to use a SELECT statement that retrieves Email field from AspNetUsers plus two constant values.

INSERT with SELECT returning multiple values throws error subquery returned more than 1 value

I'm not sure how your columns line up. It looks like GETDATE() is inserting into Name. But if that's what you need, what about this? You could just do an INSERT SELECT without the VALUES.

INSERT INTO table1 (UserId, Name, ...)
SELECT Id, GETDATE(), ...
from table2 
WHERE ClientId = 26

---

Related Topics