Sql: Insert Into...Values..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

SQL insert into table with values selected from other tables where an external value matches

My understanding of your problem is that you're trying to get ids for each of your information.

If this is correct, in order to do this you need to select their ids in three separate queries like it is done in the following query:

INSERT INTO table3 (rarityID, weakness_typeID, resistance_typeID)
SELECT (SELECT rar.id
        FROM   table1 rar
        WHERE  rar.rarity = 'Common') AS rarityID,
       (SELECT weak.id
        FROM   table2 weak
        WHERE  weak.type = 'Water') AS weakness_typeID,
       (SELECT weak.id
        FROM   table2 weak
        WHERE  weak.type = 'Air') AS resistance_typeID;

If you want to play with this code, check this SQL Fiddle.

SQL 'insert into select' into existing table

insert into Worker(WorkingPlace) 
    select WorkingPlace 
    from source.Worker;

This will create as many new rows in table Worker as there are rows returned from the select.

So if your query returns 10 rows, then 10 new rows will be inserted into table Worker

This means that any column in that table that is defined as not null must receive a value, either from your query, or get a default value when that is defined.

suppose your table looks like this

create table Worker (id int not null, WorkingPlace varchar(50) null, SomeValue int not null)

then you cannot insert a row like this

insert into Worker (WorkingPlace) values ('something')

This will fail because you have not provided a value for then column id that is defined as not null, and no value for the column SomeValue because that is also defined as not null

if your table looks like

create table Worker (id int primary key not null identity, WorkingPlace varchar(50) null, SomeValue int not null)

then the statement

insert into Worker (WorkingPlace) values ('something')

Will only fail because there is no value for the column SomeValue.

The column id will get its value from the identity

Solution:

insert into Worker (WorkingPlace, SomeValue) values ('something', 123)

INSERT to a table using SELECT and VALUES at the same time

You should be able to include the values directly in the SELECT statement:

INSERT INTO [BARS]
(
    [BUSINESSAREAID]
  , [STAFFID]
  , [ROLEID]
  , [BARSSTARTDATE]
  , [BARSENDDATE]
)
       SELECT 
           [t1].[BUSINESSAREAID]
         , [t2].[STAFFID]
         , [t3].[ROLEID]
         , @BARSSTARTDATE
         , @BARSENDDATE
       FROM [t1]
          , [t2]
          , [t3];

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)

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).

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.

---

Related Topics