INSERT INTO (SELECT & VALUES) TOGETHER

Question

I'm trying to INSERT INTO a table, and I know 2 ways:

Adding rows as values:

INSERT INTO db_example.tab_example (id,name,surname,group)
VALUES ('','Tom','Hanks','1');

or from another table:

INSERT INTO db_example.tab_example (id,name,surname)
SELECT ID,first_name,last_name FROM db_contacts.tab_mygroup;

but what if I want to insert some values from another table (the second way), and some values manually like a default value (the first way).

here's my try (it didn't work):

INSERT INTO db_example.tab_example (id,name,surname,group)
VALUES (
 SELECT ID FROM db_contacts.tab_mygroup,
 SELECT first_name FROM db_contacts.tab_mygroup,
 SELECT last_name FROM db_contacts.tab_mygroup,
 '1'
);

I thought of creating a VIEWtable and it might solve the problem, but I thought there might be someway to add both together.

Thank you guys! I hope I described well what I need :)

what was the error you got when it failed? – Hituptony Jun 16 '14 at 13:12 — Hituptony, Jun 16 '14 at 13:12
Well, you're `SELECT`ing 4 values, for one... – beercodebeer Jun 16 '14 at 13:13 — beercodebeer, Jun 16 '14 at 13:13

spencer7593 · Accepted Answer · 2014-06-16T13:55:45.067

17

Just return the literal value from the SELECT statement; add an expression to the SELECT list. For example:

INSERT INTO db_example.tab_example (id,name,surname,group)
SELECT ID
     , first_name
     , last_name
     , '1' AS group
  FROM db_contacts.tab_mygroup;

FOLLOWUP

Q: can I SELECT first_name and last_name in the same column using the AS function? or I need another function?

A: If you want to combine the values in the first_name and last_name into a single column, you could concatenate them using an expression, and use that expression in the SELECT list, e.g

CONCAT(last_name,', ',first_name')

or

CONCAT(first_name,' ',last_name)

The AS keyword won't have any effect in the context of an INSERT ... SELECT, but assigning an alias to that expression that matches the name of the column the expression is being inserted into does serve as an aid for the future reader.

INSERT INTO db_example.tab_example (id,name,surname,group,full_name)
SELECT ID
     , first_name
     , last_name
     , '1' AS group
     , CONCAT(first_name,' ',last_name) AS full_name
  FROM db_contacts.tab_mygroup

edited Jun 16 '14 at 13:55

answered Jun 16 '14 at 13:14

spencer7593

106,611
15
112
140

@rand: the **`INSERT ... VALUES`** and **`INSERT ... SELECT`** forms of the `INSERT` statement are distinct; those two forms cannot be combined. But it is possible to use a `SELECT` statement that returns literal values. – spencer7593 Jun 16 '14 at 13:30
Yes, I understood! it's just not logical the way I wrote it, but since I'm a beginner I didn't know the `AS` function. Thank you again! – rand Jun 16 '14 at 13:33
can I `SELECT` first_name and last_name in the same column using the `AS` function? or I need another function? – rand Jun 16 '14 at 13:44
@rand: if you want to combine the first_name and last_name into a single column, you could concatenate them using an expression, and use that expression in the SELECT list, e.g `CONCAT(last_name,', ',first_name')`, or `CONCAT(first_name,' ',last_name)`. The `AS` keyword won't have any effect in the INSERT ... SELECT, but assigning an alias that matches the name of the column the expression is being inserted into does serve as an aid for the future reader. – spencer7593 Jun 16 '14 at 13:53

Amanuel Nega · Answer 2 · 2019-08-19T16:36:52.940

1

Try this

INSERT INTO db_example.tab_example (id,name,surname)
SELECT id,first_name,'M. Nega'
FROM db_contacts.tab_mygroup

You can use join in the FROM clause. It should work!

edited Aug 19 '19 at 16:36

answered Jun 16 '14 at 13:34

Amanuel Nega

1,899
1
24
42

INSERT INTO (SELECT & VALUES) TOGETHER

2 Answers2

Linked

Related