Insert With Select

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

INSERT SELECT statement with only one column for SELECT

Your query will fail if the select returns more than one row. A more flexible option is to use the INSERT ... SELECT syntax and provide literal values in the additional columns:

INSERT INTO TableB (InternalEmplID, Month, Year)
SELECT InternalEmplIDFROM, 'July', '2020' TableA WHERE Name = 'John Smith'

SQL INSERT statement including a WITH and SELECT

Here is an example

INSERT
INTO    values (a,b)  //more values
WITH   table AS
    (
    SELECT  *
    FROM    table1
    )
WITH    table2 AS   //from more tables
    (
    SELECT  *
    FROM    table_2
    )         
SELECT  t.value as a, t2.value as b
FROM    table t 
JOIN table2 t2 on t.value = t2.value //some join
WHERE t.value = 'X'   //other stuff

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.

Oracle SQL: INSERT with SELECT

This would look like:

insert into book (book_id, category_id, book_name, buy_price, sell_price)
    select ?, c.category_id, ?, ?, ?
    from category c
    where c.category_name = ?;

The ? are placeholders for your constant values. Note that there are no single quotes around the column names for the insert.

INSERT with SELECT

Yes, absolutely, but check your syntax.

INSERT INTO courses (name, location, gid)
SELECT name, location, 1
FROM   courses
WHERE  cid = 2

You can put a constant of the same type as gid in its place, not just 1, of course. And, I just made up the cid value.

Insert with select posgresql

Use insert into ... select:

insert into account_role (accout_id, roles)
select id, 'COOL_ROLE'
from account
where ...

---

Related Topics