29 SQL - Insert Into... Select Statement

The SQL INSERT INTO... SELECT statement is used to add/insert one or more new rows from an existing table to another table. This statement is a combination of two different statements: INSERT INTO and SELECT.

The INSERT INTO statement is one of the most fundamental and frequently used statements in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted satisfies the constraints if the columns of a table (if any) and its type matches the data types of the table columns.
The SELECT statement is used to retrieve data from an existing database table.

When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).

Syntax

Following is the syntax of the SQL INSERT INTO... SELECT statement −

INSERT INTO table_new 
SELECT (column1, column2, ...columnN) 
FROM table_old;

Before using this query, we have to make sure that −

In the database where we are going to insert data, source and target tables already exist.
The structure of the source and target tables are same.

Example

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc.., as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Create another table named BUYERS with same structure as the CUSTOMERS table.

CREATE TABLE BUYERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query copies all the records from the CUSTOMERS table to BUYERS −

INSERT INTO BUYERS SELECT * FROM CUSTOMERS;

Verification

If you verify the contents of the BUYERS table using the SELECT statement as −

SELECT * FROM BUYERS;

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

SQL - Inserting Specific Records

Sometimes we only need to add a small number of records to another table. This can be accomplished by using a WHERE clause along with the SQL INSERT INTO... SELECT statement.

Example

Let us create a table named NAMESTARTSWITH_K with the same structure as the CUSTOMER table using the CREATE statement as −

CREATE TABLE NAMESTARTSWITH_K (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query inserts the records of the customers whose name starts with the letter k from the CUSTOMERS table to the BUYERS table −

INSERT INTO NAMESTARTSWITH_K
SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'k%';

Verification

Following is the SELECT statement to verify the contents of the above created table −

SELECT * FROM NAMESTARTSWITH_K;

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00

SQL - Inserting Top N Rows

The LIMIT clause filters the number of rows from the query. You can use this to filter the top N records that should be added to the target table.

Example

But, before proceeding further, let us truncate all rows in the BUYERS table using the following statement −

TRUNCATE TABLE BUYERS;

Following query inserts the top 3 records from the CUSTOMERS table to the BUYERS table −

INSERT INTO BUYERS 
SELECT * FROM CUSTOMERS 
ORDER BY ID ASC LIMIT 3;

Verification

Let us verify the contents of the BUYERS table −

SELECT * FROM BUYERS;

The resultant table will be as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

Search This Blog

Programming Tutorials