SQL INSERT INTO with subquery and value

Question

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

Kermit · Accepted Answer · 2013-09-12 19:28:02Z

22

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

answered Sep 12, 2013 at 19:28

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shkherad · Accepted Answer · 2017-01-20 22:51:11Z

4

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

answered Jan 20, 2017 at 22:51

shkherad

412 bronze badges

Collectives™ on Stack Overflow

SQL INSERT INTO with subquery and value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related