SQL INSERT INTO SELECT Statement

---

---

SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement in SQL is used to copy data from one table and insert it into another existing table. Unlike SELECT INTO, this statement requires that the target table already exists.

Following are some important things to remember when using this statement:

• The structure of the source and destination tables must match in terms of the number and data types of columns.
• You can copy all columns or only selected columns from one table to another.
• You can filter which rows to copy using the WHERE clause.

Syntax

Following is the basic syntax to insert all columns in a table using INSERT INTO SELECT statement:

INSERT INTO target_table
SELECT * FROM source_table;

Following is the syntax to insert specific columns in a table using INSERT INTO SELECT statement:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table;

Example: Insert all Columns

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, let us insert few records 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 follows:

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

Now, following query copies all the records from CUSTOMERS into BUYERS:

INSERT INTO BUYERS
SELECT * FROM CUSTOMERS;

We get the following output:

Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify that the data has been inserted successfully, run the following SELECT query:

SELECT * FROM BUYERS;

We get the following table:

Insert Specific Columns Using INSERT INTO SELECT

You can also insert only selected columns from one table to another existing table using the INSERT INTO SELECT statement in SQL. To do so, the columns must match in number and data type in both the source and target tables.

Example

Let us create a table named BUYERS_PARTIAL with the same structure as the CUSTOMER table:

CREATE TABLE BUYERS_PARTIAL (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY (ID)
);

Now, we insert only the ID, NAME, and AGE columns from CUSTOMERS into another table named BUYERS_PARTIAL:

INSERT INTO BUYERS_PARTIAL (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM CUSTOMERS;

Following is the output obtained:

Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

We can verify whether the columns have been inserted succesfully using the following SELECT query:

SELECT * FROM BUYERS_PARTIAL;

Following is the table obtained:

INSERT INTO SELECT with WHERE Clause

You can add a WHERE clause to the INSERT INTO SELECT statement to insert only those rows that meet specific condition (criteria).

This allows you to selectively transfer data between tables based on conditions such as age, salary, or any other column filter.

Syntax

Following is the basic syntax to use the WHERE clause with the INSERT INTO SELECT statement:

INSERT INTO target_table 
SELECT * FROM source_table
WHERE condition;

Example

In this example, we insert only customers whose AGE is greater than 25 into the BUYERS table created above:

INSERT INTO BUYERS
SELECT * FROM CUSTOMERS
WHERE AGE > 25;

Following is the output obtained:

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Verification

To verify whether the data has been inserted successfully, use the following query:

SELECT * FROM BUYERS;

Following is the table obtained:

INSERT INTO SELECT with NOT EXISTS

You can use the INSERT INTO SELECT statement along with the NOT EXISTS clause to prevent inserting duplicate records into the target table.

This is useful for transferring data based on a condition and making sure that no duplicate rows are inserted, particularly when using a unique column such as ID.

Syntax

Following is the basic syntax to use the NOT EXISTS clause with the INSERT INTO SELECT statement:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table AS src
WHERE NOT EXISTS (
  SELECT 1 FROM target_table AS tgt
  WHERE tgt.unique_column = src.unique_column
);

Example

In the following example, we insert customers from CUSTOMERS into BUYERS only if their ID does not already exist in BUYERS:

INSERT INTO BUYERS (ID, NAME, AGE, ADDRESS, SALARY)
SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS AS C
WHERE NOT EXISTS (
  SELECT 1 FROM BUYERS AS B
  WHERE B.ID = C.ID
);

We get the output as shown below:

Query OK, 0 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify that no duplicates were inserted, query the BUYERS table as follows:

SELECT * FROM BUYERS;

The table produced is as shown below:

Important Points About INSERT INTO SELECT

Following are the important points to remember when using the INSERT INTO SELECT statement in SQL:

• The destination table must already exist.
• The source and target column lists must match in count and data types.
• You can filter the source data using WHERE and avoid duplicates using NOT EXISTS or NOT IN clause.
• It is supported by most relational database systems including MySQL, SQL Server, PostgreSQL, and Oracle.