Search code examples
sqldatabasesyntaxdatabase-agnosticansi-sql-92

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?

Solution

  • 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