By Claude Houle


2008-08-25 12:45:56 8 Comments

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?

23 comments

@Mohammed Safeer 2019-04-04 04:09:13

Two approaches for insert into with select sub-query.

  1. With SELECT subquery returning results with One row.
  2. With SELECT subquery returning results with Multiple rows.

1. Approach for With SELECT subquery returning results with one row.

INSERT INTO <table_name> (<field1>, <field2>, <field3>) 
VALUES ('DUMMY1', (SELECT <field> FROM <table_name> ),'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 <table_name> (<field1>, <field2>, <field3>) 
SELECT 'DUMMY1', <field>, 'DUMMY2' FROM <table_name>;

The second approach will work for both the cases.

@Dasikely 2018-09-24 09:25:16

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'
);

@Gaurav 2018-02-13 16:35:20

INSERT INTO FIRST_TABLE_NAME (COLUMN_NAME)
SELECT  COLUMN_NAME
FROM    ANOTHER_TABLE_NAME 
WHERE CONDITION;

@ggorlen 2018-08-13 01:31:44

Please describe your insert statement and how it answers the question.

@reggaeguitar 2019-04-19 16:38:56

@ggorlen It looks pretty self evident to me

@ggorlen 2019-04-19 16:48:46

It was flagged in the review queue as a code-only answer. I can see your point here, though--there's not much to say in the context of most answers on this page now that I'm seeing it in its natural environment.

@logan 2015-04-21 10:31:32

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

@Manish Vadher 2018-06-07 06:59:35

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)

@Claude Houle 2008-08-25 12:47:54

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

@Sebastian 2017-06-09 03:51:51

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.

@Quality Catalyst 2017-11-21 05:34:49

The question is "insert into a table using the input from another table". How does your answer address this question?

@Cameron Belt 2018-02-15 15:19:33

Eh dont be too hard on him. It answered my question when I was googling around. @QualityCatalyst

@travis 2008-08-25 14:11:15

@Shadow_x99: That 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;

Edit: I should mention that I've only used this syntax with Access, SQL 2000/2005/Express, MySQL, and PostgreSQL, so those should be covered. A commenter has pointed out that it'll work with SQLite3.

@vijayrana 2016-03-17 06:25:06

what if, the where condition changed to table2.country and returns number of rows greater than one? I got similar issue here: stackoverflow.com/questions/36030370/…

@rinukkusu 2016-04-11 07:24:09

There shouldn't be a problem with inserting more than one row.

@Bharath theorare 2016-06-17 10:28:45

You could try this if you want to insert all column using SELECT * INTO table.

SELECT  *
INTO    Table2
FROM    Table1;

@Ciaran Bruen 2016-03-23 14:31:52

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.)

@RameezAli 2014-02-13 12:23:26

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

@Matt 2015-05-20 08:44:14

INSERT INTO yourtable
SELECT fielda, fieldb, fieldc
FROM donortable;

This works on all DBMS

@Weslor 2015-04-09 17:15:42

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);

@snowfox 2016-04-26 02:10:32

This approach only applies to such subquery that only one column is selected. In the case of multiple-column subquery, an error 'subquery must return only one column' will be raised. Adopt @travis's answer then.

@Faiz 2012-10-17 16:56:44

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 <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table       <<<<------- Look here ------------------------
        | execute_statement   <<<<------- Look here ------------------------
        | <dml_table_source>  <<<<------- 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.

@kylie.a 2014-01-10 23:46:12

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))

@ZaLiTHkA 2014-07-23 14:16:27

Just for clarification: this is incorrect for SQLite3. As per the documentation, the source data for the INSERT is either VALUES or a SELECT statement, not both.

@Banjocat 2014-10-21 15:32:09

It is true the documentation does not list it, but it does work. Regardless, I think that using the select statement instead of values does make it more readable.

@Luchostein 2015-11-24 14:33:17

It works for specifying a value inside a row, but the more general case requires getting lots of rows.

@Chris B 2016-09-16 00:28:46

If val_1 doesn't change across rows, then the following syntax might work in SQLite3? select 'foo', some_column from some_table - works in SQLServer 2014

@zapl 2018-07-03 18:06:40

The documentation does list this (now?): this syntax is INSERT INTO ... VALUES ([expr], [expr], ...) and one of the paths in [expr] is {{NOT} EXISTS} ([select-stmt]) - note that the paranthesis around the select statement are required ({} meaning optional)

@Lee 2018-11-18 04:43:08

what if the select return more than 1 row?

@Mohammed Noureldin 2018-11-29 14:24:49

I confirm that this works pretty well

@kylie.a 2018-11-29 22:16:54

@Lee That's a great question. I think when I was working with this val2 may have been a PK or some other unique field so it never came up.

@Pavel 2014-05-17 20:28:32

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

@Sarvar Nishonboev 2014-03-20 09:12:35

This is another example using values with select:

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

@Sebastian Kaczmarek 2018-05-17 10:23:32

Old answer and still useful. Pretty simple and obvious but exactly covers my needs. Thanks!

@elijah7 2013-11-20 12:19:28

This worked for me:

insert into table1 select * from table2

The sentence is a bit different from Oracle's.

@Santhosh 2013-04-01 10:09:09

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

@SWATI BISWAS 2012-06-06 12:41:10

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;

@Grungondola 2013-03-22 14:57:06

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.

@northben 2012-10-16 14:19:52

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.

@Jonathan Leffler 2008-09-28 03:18:41

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:

[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
[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.

Related Questions

Sponsored Content

37 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2570317 View
  • 1735 Score
  • 29 Answer
  • Tags:   sql duplicates

7 Answered Questions

29 Answered Questions

[SOLVED] How to select the nth row in a SQL database table?

27 Answered Questions

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

27 Answered Questions

[SOLVED] SQL select only rows with max value on a column

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

8 Answered Questions

[SOLVED] 'IF' in 'SELECT' statement - choose output value based on column values

  • 2011-05-10 13:53:31
  • Michael
  • 1325370 View
  • 659 Score
  • 8 Answer
  • Tags:   mysql sql database

Sponsored Content