17

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
  • 33,827
  • 13
  • 85
  • 121
Jaiesh_bhai
  • 1,778
  • 8
  • 26
  • 41

2 Answers2

21

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';
Kermit
  • 33,827
  • 13
  • 85
  • 121
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';
shkherad
  • 41
  • 2