CODEWITHSUNDEEP
sqlsql-serversql-insert
A. Gladkiy
A. Gladkiy

Reputation: 3450

sql insert multiple rows from another table

I have tables:

ContainerSubTypes (Id, Name) with many records,

WorkAreas (Id, Name) with only one row,

WorkAreaContainerSubTypes table (Id, WorkAreaId, ContainerSubTypeId) - empty table.

I need to insert data into WorkAreaContainerSubTypes table, it should look like:

(1, 1, containersubtype1_id)
(2, 1, containersubtype2_id)
(3, 1, containersubtype3_id)

And I'm trying sql like:

INSERT INTO WorkAreaContainerSubTypes (WorkAreaId, ContainerSubTypeId)
    VALUES
    (1, (SELECT Id FROM ContainerSubTypes));

But SELECT query has more than 1 row and it throws error. How can I implement that inserting?

Upvotes: 0

Views: 1339

Answers (2)

Zhorov
Zhorov

Reputation: 30003

VALUES table value constructor specifies a set of row value expressions to be constructed into a table, so you need a different statement:

INSERT INTO WorkAreaContainerSubTypes (WorkAreaId, ContainerSubTypeId)
SELECT 1, Id 
FROM ContainerSubTypes;

As an additional note, you may use VALUES only if SELECT Id FROM ContainerSubTypes statement returns a single row or no rows (perhaps with an optional WHERE clause):

INSERT INTO WorkAreaContainerSubTypes (WorkAreaId, ContainerSubTypeId)
VALUES
   (1, (SELECT Id FROM ContainerSubTypes WHERE Name = 'Some name'));

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Just use insert . . . select:

INSERT INTO WorkAreaContainerSubTypes (WorkAreaId, ContainerSubTypeId)
    SELECT 1, Id
    FROM ContainerSubTypes;

Upvotes: 1

Related Questions