CODEWITHSUNDEEP
sqlsql-insert
Jaiesh_bhai
Jaiesh_bhai

Reputation: 1814

SQL INSERT INTO with subquery and value

Is there a way I can use a combination of hard values and a subquery to insert into a table with one command?

For example: 

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

I need supplier_type to be 3. So can I do the following for the second line?

SELECT account_no, name, supplier_type = 3

supplier_type is not in the customers table

Upvotes: 19

Views: 43177

Answers (2)

shkherad
shkherad

Reputation: 41

Even simpler, just fill in the field with the value, dont even need an AS:

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

Upvotes: 4

Kermit
Kermit

Reputation: 34055

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';

Upvotes: 22

Related Questions