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?



Answer:

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


http://stackoverflow.com/questions/25969/insert-into-values-select-from


TAGS : | |