Insert into ... Values ( Select ... from ... )

Insert into ... values ( SELECT ... FROM ... )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

• Oracle
• MS SQL Server
• MySQL
• Postgres
• SQLite v3
• Teradata
• DB2
• Sybase
• Vertica
• HSQLDB
• H2
• AWS RedShift
• SAP HANA
• Google Spanner

INSERT INTO (SELECT & VALUES) TOGETHER

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

How to insert custom values with INSERT INTO + SELECT FROM?

INSERT INTO RoleMappingEmployee_Delete_History (
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    DeletedBy,
    DeletedDateTime)
SELECT 
    RoleMappingEmployeeKey,
    SrKey,
    RoleKey,
    SubmittedDate,
    SubmittedBy,
    IsActive,
    'peter',
    getdate()
FROM
    RoleMappingEmployee
WHERE
    RoleMappingEmployeeKey IN (25902,38188,25887)

Select inside an insert into.. values(...)

Simply enclose the select with parenthesis:

INSERT INTO SMS(N_SMS, CODIGO_CLIENTE) 
    VALUES (SEQ_SMS.NEXTVAL
            , (SELECT CODIGO_CLIENTE 
               FROM ORDEM_SERVICO, VEICULO 
               WHERE ORDEM_SERVICO.MATRICULA = VEICULO.MATRICULA)
           )

Also, be sure that the select returns only one row and one column.

INSERT INTO SELECT vs. INSERT INTO VALUES ... (SELECT)

The first method is generally better, because the second method runs the risk of doing one of two things:

• Generating an error if the subquery returns more than one row.
• Inserting a NULL value if the subquery returns no rows.

Of course, that might be desirable behavior.

In general, insert . . . select is more general than insert . . . values(). I prefer the former because it is more powerful and more useful in more situations (including all situations where values can be used).

INSERT value using SELECT in mysql

Your SQL query is incorrect for several reasons.

The following should work if I have interpreted your query correctly.

INSERT INTO `login_attempts`(`user_id`, `time`, `failed`)
SELECT id, '', 3 FROM users WHERE username = 'pokemon'

INSERTing into a table from a SELECT does not require VALUES ( ... ). Here is an example of how you would use VALUES ( ... ):

INSERT INTO `login_attempts`(`user_id`, `time`, `failed`)
VALUES (1, '', 3)

Also, your sub query SELECT user_id FROM users WHERE username = 'pokemon','',3 the WHERE clause is invalid. Specifically the '',3 part which I assume is the values you wanted to insert for time and failed.

Insert Into with value select from the same table

Don't use VALUES but SELECT where you add the literal values that you want inserted among the other columns:

insert Into Application_Form_Controls_Management 
(column1, App_Form_Name,App_Form_Function_ID,Right_ID,column5, _)
select 'manual value',App_Form_Name,App_Form_Function_ID,Right_ID,'the rest are manual values'
from Application_Form_Controls_Management 
where App_Form_Name IN (Select Application_Form_Name From FormNameGridNameTempTable)
group by App_Form_Name,App_Form_Function_ID,Right_ID

MySQL INSERT INTO ... VALUES and SELECT

Use an insert ... select query, and put the known values in the select:

insert into table1
select 'A string', 5, idTable2
from table2
where ...

How to insert values into a table from the result of a SELECT statement SQL

You were almost there. Try this.

INSERT INTO PLAYERS(name,team,goals,cod) 
SELECT name,team,goals,1 FROM NEW_PLAYERS

---

Related Topics