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

User · Answer

INSERT INTO FIRST TABLE NAME  COLUMN NAME  SELECT  COLUMN NAME FROM    ANOTHER TABLE NAME  WHERE CONDITION

User · Answer

If you go the INSERT VALUES route to insert multiple rows  make sure to delimit the VALUES into sets using parentheses  so       INSERT INTO  receiving table     id    first name    last name  VALUES     1002  Charles   Babbage       1003  George    Boole       1001  Donald   Chamberlin       1004  Alan   Turing       1005  My   Widenius      Otherwise MySQL objects that  Column count doesn t match value count at row 1   and you end up writing a trivial post when you finally figure out what to do about it

User · Answer

In informix it works as Claude said   INSERT INTO table  column1  column2   VALUES  value1  value2

User · Answer

INSERT INTO yourtable SELECT fielda  fieldb  fieldc FROM donortable    This works on all DBMS

User · Answer

I actually prefer the following in SQL Server 2008   SELECT Table1 Column1  Table1 Column2  Table2 Column1  Table2 Column2   Some String  AS SomeString  8 AS SomeInt INTO Table3 FROM Table1 INNER JOIN Table2 ON Table1 Column1   Table2 Column3   It eliminates the step of adding the Insert    set  and you just select which values go in the table

User · Answer

Two approaches for insert into with select sub-query     With SELECT subquery returning results with One row  With SELECT subquery returning results with Multiple rows    1  Approach for With SELECT subquery returning results with one row   INSERT INTO  lt table name gt    lt field1 gt    lt field2 gt    lt field3 gt    VALUES   DUMMY1    SELECT  lt field gt  FROM  lt table name gt     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  lt table name gt    lt field1 gt    lt field2 gt    lt field3 gt    SELECT  DUMMY1    lt field gt    DUMMY2  FROM  lt table name gt     The second approach will work for both the cases

User · 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 SAP HANA Google Spanner

User · Answer

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

User · Answer

Just use parenthesis for SELECT clause into INSERT  For example like this    INSERT INTO Table1  col1  col2  your desired value from select clause  col3  VALUES       col1 value        col2 value       SELECT col Table2 FROM Table2 WHERE IdTable2    your satisfied value for col Table2 selected        col3 value

User · Answer

For Microsoft SQL Server  I will recommend learning to interpret the SYNTAX provided on MSDN  With Google it s easier than ever  to look for syntax   For this particular case  try      Google  insert site microsoft com   The first result will be http   msdn microsoft com en-us library ms174335 aspx  scroll down to the example   Using the SELECT and EXECUTE options to insert data from other tables   if you find it difficult to interpret the syntax given at the top of the page     WITH  lt common table expression gt        n     INSERT              TOP   expression     PERCENT                INTO               lt object gt    rowset function limited              WITH    lt Table Hint Limited gt       n                                   column list                 lt OUTPUT Clause gt              VALUES     DEFAULT   NULL   expression         n           n                  derived table        lt  lt  lt  lt ------- Look here ------------------------           execute statement    lt  lt  lt  lt ------- Look here ------------------------            lt dml table source gt    lt  lt  lt  lt ------- Look here ------------------------           DEFAULT VALUES                          This should be applicable for any other RDBMS available there  There is no point in remembering all the syntax for all products IMO

User · Answer

You could try this if you want to insert all column using SELECT   INTO table   SELECT    INTO    Table2 FROM    Table1

User · Answer

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

User · Answer

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  DB2  SQL Server  MY SQL  PostgresQL

User · Answer

Both the answers I see work fine in Informix specifically  and are basically standard SQL   That is  the notation   INSERT INTO target table   lt column-list gt    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

User · Answer

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

User · Answer

IF you want to insert some data into a table without want to write column name   INSERT INTO CUSTOMER INFO     SELECT CUSTOMER NAME             MOBILE NO             ADDRESS       FROM OWNER INFO cm    Where the tables are               CUSTOMER INFO                             OWNER INFO ----------------------------------------  ------------------------------------- CUSTOMER NAME   MOBILE NO   ADDRESS        CUSTOMER NAME   MOBILE NO   ADDRESS  -------------- ----------- ---------       -------------- ----------- ---------        A              1        DC                 B              55       RR     Result               CUSTOMER INFO                             OWNER INFO ----------------------------------------  ------------------------------------- CUSTOMER NAME   MOBILE NO   ADDRESS        CUSTOMER NAME   MOBILE NO   ADDRESS  -------------- ----------- ---------       -------------- ----------- ---------        A              1        DC                 B              55       RR       B              55       RR

User · Answer

Here s how to insert from multiple tables  This particular example is where you have a mapping table in a many to many scenario   insert into StudentCourseMap  StudentId  CourseId   SELECT  Student Id  Course Id FROM Student  Course  WHERE Student Name    Paddy Murphy  AND Course Name    Basket weaving for beginners     I realise matching on the student name might return more than one value but you get the idea  Matching on something other than an Id is necessary when the Id is an Identity column and is unknown

User · Answer

select   into tmp from orders   Looks nice  but works only if tmp doesn t exists  creates it and fills    SQL sever   To insert into existing tmp table    set identity insert tmp on  insert tmp    OrderID          CustomerID          EmployeeID          OrderDate          RequiredDate          ShippedDate          ShipVia          Freight          ShipName          ShipAddress          ShipCity          ShipRegion          ShipPostalCode          ShipCountry          select   from orders  set identity insert tmp off

User · Answer

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

User · Answer

This can be done without specifying the columns in the INSERT INTO part if you are supplying values for all columns in the SELECT part   Let s say table1 has two columns  This query should work   INSERT INTO table1 SELECT  col1  col2 FROM    table2   This WOULD NOT work  value for col2 is not specified    INSERT INTO table1 SELECT  col1 FROM    table2   I m using MS SQL Server  I don t know how other RDMS work

User · Answer

Instead of VALUES part of INSERT query  just use SELECT query as below   INSERT INTO table1   column1   2  3      SELECT col1  2  3    FROM table2

User · Answer

This worked for me   insert into table1 select   from table2   The sentence is a bit different from Oracle s

User · Answer

Here is another example where source is taken using more than one table   INSERT INTO cesc pf stmt ext wrk     PF EMP CODE        PF DEPT CODE       PF SEC CODE        PF PROL NO         PF FM SEQ          PF SEQ NO          PF SEP TAG         PF SOURCE   SELECT   PFl EMP CODE        PFl DEPT CODE       PFl SEC             PFl PROL NO         PF FM SEQ           PF SEQ NO           PFl SEP TAG         PF SOURCE  FROM cesc pf stmt ext        cesc pfl emp master  WHERE pfl sep tag LIKE  0     AND pfl emp code pf emp code      COMMIT

User · Answer

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

User · Answer

Postgres supports next  create table company monitor2 as select   from company monitor

User · Answer

This is another example using values with select   INSERT INTO table1 desc  id  email   SELECT  Hello World   3  email FROM table2 WHERE

[sql] Insert into ... values ( SELECT ... FROM ... )

Examples related to sql

Examples related to database

Examples related to syntax

Examples related to database-agnostic

Examples related to ansi-sql-92