SQL Insert into with Subquery and Value

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.

SQL INSERT INTO with subquery and value

Just add it with your SELECT fields.

INSERT INTO suppliers (supplier_id, supplier_name, supplier_type)
SELECT account_no, name, 3 AS supplier_type
FROM customers
WHERE city = 'San Diego';

SQL Server 2014: INSERT INTO values & subqueries

Use the select as-is and include the static values in the same statement.

INSERT INTO TESTPOOLS (SaleEventID, PoolID, PoolName, CurrentUPB)
SELECT DISTINCT '55', [POOLID], 'SouthernFL', '45,000'
FROM TESTLOANS
WHERE SaleEventID=0

Edit: Use window functions to get the counts or sums.

For eg:

INSERT INTO TESTPOOLS (SaleEventID, PoolID, PoolName, CurrentUPB,somecount)
SELECT DISTINCT '55', [POOLID], 'SouthernFL'
,sum(currentUPB) over(partition by poolid) --if you don't need the sum by poolid just use sum(currentUPB) over()
,count(*) over(partition by poolid) --if you don't need the count by poolid just use count(*) over()
FROM TESTLOANS
WHERE SaleEventID=0

Insert statement with subquery which has inner join statement

What you're doing here, however, is mixing INSERT ...VALUES and INSERT...SELECT syntax. The documentation provides an example on how to perform a INSERT...SELECT statement: Inserting Data from Other Tables. You need to drop the parenthesis (()) around your SELECT, and remove the VALUES clause. So:

INSERT INTO [OS].[OldSyllabus] (StudenName) --Should that be StudentName? 
SELECT C.Name
FROM [COL].[College] AS c
     INNER JOIN [UNI].[University] AS u ON c.CourseName = u.CourseName
                                       AND c.Date <= u.Date;

INSERT INTO with SubQuery MySQL

Use numeric literals with aliases inside a SELECT statement. No () are necessary around the SELECT component.

INSERT INTO qa_costpriceslog (item_code, invoice_code, item_costprice)
  SELECT
    /* Literal number values with column aliases */
    1 AS item_code,
    2 AS invoice_code,
    item_costprice
  FROM qa_items 
  WHERE item_code = 1;

Note that in context of an INSERT INTO...SELECT, the aliases are not actually necessary and you can just SELECT 1, 2, item_costprice, but in a normal SELECT you'll need the aliases to access the columns returned.

Can a subquery be used with the VALUES keyword?

You need to insert subqueries in parentheses. The opening paren for values doesn't count. It is the start of a list, not a subquery. You can include subqueries in the VALUES clause when they return one row and one column.

Instead, though, you can use this syntax:

insert into regions (region_id, region_name)
    select max(region_id) + 1, 'Great Britain'
    from regions;

Better yet would be to assign a sequence to the region_id (identity or auto-increment column in other databases) so it is assigned automatically. Then you would just do:

insert into regions (region_name)
    select 'Great Britain'
    from dual;

Insert into Multiple Columns from a Grouped by Subquery

try like below if want to use subsqery

INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  cnt from                                          
(SELECT CATEGORY, COUNT(1) as cnt FROM SUB_TABLE GROUP BY CATEGORY) a

in fact don't need sub-query

 INSERT INTO TABLE (COLUMN_A, COLUMN_B, COLUMN_C)              -
SELECT 'Label A', CATEGORY,  COUNT(1) as cnt from                                          
  FROM SUB_TABLE GROUP BY CATEGORY

demo link

INSERT INTO with subquery and text value at once

Should be able exclude the columns you don't want as long as they aren't required fields and then you can join to your offices table.

INSERT INTO employees (lastName, firstName, officeCode, extension, email, jobTitle)
SELECT contactLastName, contactFirstName, officeCode, ‘999’, ‘email@email.com’, ‘Manager’
FROM   customers
       LEFT JOIN offices ON offices.country = customers.country
WHERE  customers.country = 'USA'  

---

Related Topics