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?

Answer 1

This is another example using values with select:

INSERT INTO table1(desc, id, email) 
SELECT "Hello World", 3, email FROM table2 WHERE ...

Answer 2

Simple insertion when table column sequence is known:

    Insert into Table1
    values(1,2,...)

Simple insertion mentioning column:

    Insert into Table1(col2,col4)
    values(1,2)

Bulk insertion when number of selected columns of a table(#table2) are equal to insertion table(Table1)

    Insert into Table1 {Column sequence}
    Select * -- column sequence should be same.
       from #table2

Bulk insertion when you want to insert only into desired column of a table(table1):

    Insert into Table1 (Column1,Column2 ....Desired Column from Table1)  
    Select Column1,Column2..desired column from #table2
       from #table2

Answer 3

This worked for me:

insert into table1 select * from table2

The sentence is a bit different from Oracle's.

Answer 4

Best way to insert multiple records from any other tables.

INSERT  INTO dbo.Users
            ( UserID ,
              Full_Name ,
              Login_Name ,
              Password
            )
            SELECT  UserID ,
                    Full_Name ,
                    Login_Name ,
                    Password
            FROM    Users_Table
            (INNER JOIN / LEFT JOIN ...)
            (WHERE CONDITION...)
            (OTHER CLAUSE)