Insert query with a subquery

Question

I want to insert somewhere around 400 rows into a table. All of the column values will be the same fixed value for most of the columns, with exception to one column which I want to pull from a fairly complicated subquery that will pull over 400 unique values from another table using a series of joins and where statements. We attempted the following query:

INSERT INTO mytable (col1, col2, col3, col4)
VALUES(val1, (subquery), val2, val3)

It stated, that it wouldn't work because the subquery yielded more than one result. The only values being pulled from a different table is in the subquery. I cannot insert the subquery into the respective column by itself, since col1 is a NOT NULL column.

• ypercubeᵀᴹ: Is the question for SQL-Server or some other DBMS?
• Eric Ly: The database is telling you the problem. If your subquery yields more than one row, how can the database insert it in a single row ? You should specify a query that yields only one row for each inserted row.

Answer 1

If the (subquery) has SELECT (whatever expression) AS col FROM ..., then you can do:

INSERT INTO mytable 
 (col1, col2, col3, col4) 
SELECT 
 val1, s.col, val2, val3
FROM 
 (subquery) AS s ;

or:

WITH s (col) AS
 (subquery)
INSERT INTO mytable 
 (col1, col2, col3, col4) 
SELECT 
 val1, s.col, val2, val3
FROM 
 s ;

• jdillman: That worked perfectly for what I wanted to do. I'm new to database management so your info will help me out on a lot of the stuff I need to do.

Answer 2

Simple , just try to adapt the following code

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