Insert into ... values ( SELECT ... FROM ... )
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?
INSERT INTO table1 ( column1 ) SELECT col1 FROM table2
This is standard ANSI SQL and should work on any DBMS
It definitely works for:
- MS SQL Server
- SQLite v3
- AWS RedShift
- SAP HANA
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.
Read more... Read less...
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))
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:
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 [email protected]:table [email protected]: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.
To add something in the first answer, when we want only few records from another table (in this example only one):
INSERT INTO TABLE1 (COLUMN1, COLUMN2, COLUMN3, COLUMN4) VALUES (value1, value2, (SELECT COLUMN_TABLE2 FROM TABLE2 WHERE COLUMN_TABLE2 like "blabla"), value4);
Most of the databases follow the basic syntax,
INSERT INTO TABLE_NAME SELECT COL1, COL2 ... FROM TABLE_YOU_NEED_TO_TAKE_FROM ;
Every database I have used follow this syntax namely,
VALUES part of
INSERT query, just use
SELECT query as below.
INSERT INTO table1 ( column1 , 2, 3... ) SELECT col1, 2, 3... FROM table2