SQL INSERT INTO SELECT Statement


The SQL INSERT INTO SELECT statement copies data (all or selected by user) from one database table and inserts it into an existing database table.

By default, the SQL INSERT INTO statement will copy only one records at a time. But we can use the SQL INSERT INTO SELECT statement to copy a single records or multiple records at once.

Note: In this way, if you copy data from source table and any existing records in the target table are unaffected by this action.


Search Keys

  • sql insert into select
  • insert into select sql server
  • insert into select from
  • sql server insert into select from table
  • sql query insert into select
  • sql insert into select from same table
  • multiple insert sql
  • multiple insert into sql
  • insert into sql
  • insert into syntax
  • insert into from select
  • insert into where
  • insert into table
  • insert into values

SQL INSERT INTO SELECT Syntax

The below syntax is used to select all column(s) and records from one table to another, existing table.


INSERT INTO table_name2
SELECT * FROM table_name1;

The below syntax is used to select specific column(s) from one table to another, existing table.


INSERT INTO table_name2
(column_name_list(s))
SELECT column_name_list(s)
FROM table_name1;

Note: The above syntax is very basic one. The entire sql statement can contain SQL WHERE, SQL ORDER BY, and SQL GROUP BY clauses, as well as table JOINS and Aliases.


Sample Database Table - Book1

BookId BookName Description
101 Sql Complete Reference It descripes how to write and execute SQL statement into database.
102 Sql Commands It exaplins a list of SQL command.
103 Pl Sql Quick Programming How to write and execute SQL programming using pl sql.

Sample Database Table - Book2

BookId AuthorName DomainName BookPrice
101 Suresh Babu Database 250.5
102 Siva Kumar Programming 120

Sample Database Table - Books3

BId BName BPrice AuthorName BDomain
1 MySQL 5.0 Quick Reference 140 Suresh Babu Database
2 Microsoft SQL Server Database 100 Haris Karthik Database
3 Pl / Sql programming guide 50 Siva Kumar Programming

SQL INSERT INTO SELECT Example

The following SQL SELECT statement will copy only a few columns from table "Book1" into table "Book3"


INSERT INTO Book3 (BId, BName)
SELECT BookID, BookName FROM Book1;

The above sql statement will copy all data "BookID" and "BookName" columns from "Book1" table and inserts into "BID" and "BName" columns from "Book2" table.

After executing the above statement, the "Book3" table will look like this:

BId BName BPrice AuthorName BDomain
1 MySQL 5.0 Quick Reference 140 Suresh Babu Database
2 Microsoft SQL Server Database 100 Haris Karthik Database
3 Pl / Sql programming guide 50 Siva Kumar Programming
101 Sql Complete Reference
102 Sql Commands
103 Pl Sql Quick Programming

The following SQL SELECT statement will copy only the "Programming" domain from "Book1" into "Book3"


INSERT INTO Book3 (BPrice, AuthorName, BDomain)
SELECT BookPrice, AuthorName, DomainName FROM Book2
WHERE DomainName = 'Programming';

The above sql statement will copy all data "BookPrice", "AuthorName", and "BDomain" columns from "Book2" table and inserts into "BPrice", "AuthorName", and "BDomain" columns from "Book3" table.

After executing the above statement, the "Book3" table will look like this:

BId BName BPrice AuthorName BDomain
1 MySQL 5.0 Quick Reference 140 Suresh Babu Database
2 Microsoft SQL Server Database 100 Haris Karthik Database
3 Pl / Sql programming guide 50 Siva Kumar Programming
101 Sql Complete Reference
102 Sql Commands
103 Pl Sql Quick Programming
120 Siva Kumar Programming

Note: There will be a NULL value in the not selected columns.


Search Keys

  • insert into values select
  • sql insert into table from select
  • sql insert into example
  • sql insert into values select
  • insert into existing table
  • insert into with select
  • insert into sql table
  • select from insert into
  • insert select values
  • add row to sql table
  • sql query add row
  • add record sql
  • sql add data to table
  • sql insert into table values