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

Question

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (MySQL, Oracle, SQL Server, Informix, and DB2).

Is there a silver-bullet syntax coming from an SQL standard (for example, SQL-92) that would allow me to insert the values without worrying about the underlying database?

Solution 1

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

Solution 2

Claude Houle's answer: should work fine, and you can also have multiple columns and other data as well:

INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT  table2.column1, table2.column2, 8, 'some string etc.'
FROM    table2
WHERE   table2.ID = 7;

I've only used this syntax with Access, SQL 2000/2005/Express, MySQL, and PostgreSQL, so those should be covered. It should also work with SQLite3.

Solution 3

To get only one value in a multi value INSERT from another table I did the following in SQLite3:

INSERT INTO column_1 ( val_1, val_from_other_table ) 
VALUES('val_1', (SELECT  val_2 FROM table_2 WHERE val_2 = something))

Solution 4

Both the answers I see work fine in Informix specifically, and are basically standard SQL. That is, the notation:

INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...;

works fine with Informix and, I would expect, all the DBMS. (Once upon 5 or more years ago, this is the sort of thing that MySQL did not always support; it now has decent support for this sort of standard SQL syntax and, AFAIK, it would work OK on this notation.) The column list is optional but indicates the target columns in sequence, so the first column of the result of the SELECT will go into the first listed column, etc. In the absence of the column list, the first column of the result of the SELECT goes into the first column of the target table.

What can be different between systems is the notation used to identify tables in different databases - the standard has nothing to say about inter-database (let alone inter-DBMS) operations. With Informix, you can use the following notation to identify a table:

[dbase[@server]:][owner.]table

That is, you may specify a database, optionally identifying the server that hosts that database if it is not in the current server, followed by an optional owner, dot, and finally the actual table name. The SQL standard uses the term schema for what Informix calls the owner. Thus, in Informix, any of the following notations could identify a table:

table
"owner".table
dbase:table
dbase:owner.table
dbase@server:table
dbase@server:owner.table

The owner in general does not need to be quoted; however, if you do use quotes, you need to get the owner name spelled correctly - it becomes case-sensitive. That is:

someone.table
"someone".table
SOMEONE.table

all identify the same table. With Informix, there's a mild complication with MODE ANSI databases, where owner names are generally converted to upper-case (informix is the exception). That is, in a MODE ANSI database (not commonly used), you could write:

CREATE TABLE someone.table ( ... )

and the owner name in the system catalog would be "SOMEONE", rather than 'someone'. If you enclose the owner name in double quotes, it acts like a delimited identifier. With standard SQL, delimited identifiers can be used many places. With Informix, you can use them only around owner names -- in other contexts, Informix treats both single-quoted and double-quoted strings as strings, rather than separating single-quoted strings as strings and double-quoted strings as delimited identifiers. (Of course, just for completeness, there is an environment variable, DELIMIDENT, that can be set - to any value, but Y is safest - to indicate that double quotes always surround delimited identifiers and single quotes always surround strings.)

Note that MS SQL Server manages to use [delimited identifiers] enclosed in square brackets. It looks weird to me, and is certainly not part of the SQL standard.

Solution 5

Two approaches for insert into with select sub-query.

1. With SELECT subquery returning results with One row.
2. With SELECT subquery returning results with Multiple rows.

1. Approach for With SELECT subquery returning results with one row.

INSERT INTO <table_name> (<field1>, <field2>, <field3>) 
VALUES ('DUMMY1', (SELECT <field> FROM <table_name> ),'DUMMY2');

In this case, it assumes SELECT Sub-query returns only one row of result based on WHERE condition or SQL aggregate functions like SUM, MAX, AVG etc. Otherwise it will throw error

2. Approach for With SELECT subquery returning results with multiple rows.

INSERT INTO <table_name> (<field1>, <field2>, <field3>) 
SELECT 'DUMMY1', <field>, 'DUMMY2' FROM <table_name>;

The second approach will work for both the cases.

View more solutions

Comments

[Uğur Gümüşhan]

this example works: insert into tag_zone select @tag,zoneid,GETDATE(),@positiong.STIntersects(polygon) from zone

[Admin]

Just for clarification: this is incorrect for SQLite3. As per the documentation, the source data for the INSERT is either VALUES or a SELECT statement, not both.

[Banjocat]

It is true the documentation does not list it, but it does work. Regardless, I think that using the select statement instead of values does make it more readable.

[Luchostein]

It works for specifying a value inside a row, but the more general case requires getting lots of rows.

[VijayRana]

what if, the where condition changed to table2.country and returns number of rows greater than one? I got similar issue here: stackoverflow.com/questions/36030370/…

[rinukkusu]

There shouldn't be a problem with inserting more than one row.

[snowfox]

This approach only applies to such subquery that only one column is selected. In the case of multiple-column subquery, an error 'subquery must return only one column' will be raised. Adopt @travis's answer then.

[Chris B]

If val_1 doesn't change across rows, then the following syntax might work in SQLite3? select 'foo', some_column from some_table - works in SQLServer 2014

[Quality Catalyst]

The question is "insert into a table using the input from another table". How does your answer address this question?

[Darth Scitus]

Eh dont be too hard on him. It answered my question when I was googling around. @QualityCatalyst

[Sebastian Kaczmarek]

Old answer and still useful. Pretty simple and obvious but exactly covers my needs. Thanks!

[zapl]

The documentation does list this (now?): this syntax is INSERT INTO ... VALUES ([expr], [expr], ...) and one of the paths in [expr] is {{NOT} EXISTS} ([select-stmt]) - note that the paranthesis around the select statement are required ({} meaning optional)

[reggaeguitar]

@ggorlen It looks pretty self evident to me

[ggorlen]

It was flagged in the review queue as a code-only answer. I can see your point here, though--there's not much to say in the context of most answers on this page now that I'm seeing it in its natural environment.

[reggaeguitar]

I completely disagree, I've been looking at those syntax statements for years and still can't make heads or tails of them. Examples are much more useful

[reggaeguitar]

This isn't an answer, it's saying "read the docs" and that's about it

[Mahesh Jamdade]

is it Necessary that we insert into all columns of the table

[travis]

@maheshmnj no, only the columns that are set to NOT NULL and no default value need to be included, any other columns will be set to their default values or NULL

[MindRoasterMir]

How to implement this ? "insert into Content as c (ContentP) select title from title as t wehre c.pageno = t.pageno" . THanks

[Shadros]

Perfect for me, Thanks

[AmigoJack]

I highly doubt all of them supported it right away - only SQLite has a minimum version, but it would also be interesting for others, especially ORACLE.

[Raj G]

If you save whole table as backup use this query..select * INTO TableYedek_Backup From TableYedek

[RJD]

@MindRoasterMir You can't do that. If you perform an insert you will create a new row in the table hence you cannot have an existing row with which you can compare (c.pageno = t.pageno). You are probably looking for an UPDATE or MERGE (MS Sql)

[Farah Nazifa]

Not working for AWS redshift - getting error - XX000: Value too long for character type

[sametcilli]

IF you want to handle duplicate row according to primary key or unique index, you can use IGNORE option like INSERT IGNORE INTO table SELECT * FROM another_table

[Markus Pscheidt]

Not supported by H2