Written By:
iamabhishek
4 minute read
SQLDBMSMySQLOracle

How to SELECT data from one Table and INSERT into Another

Posted in Programming   LAST UPDATED: JUNE 8, 2021

If you have 2 tables in your database, and you want to SELECT data from one table and INSERT it into another table, this is possible using an INSERT...SELECT statement.

Using an INSERT...SELECT statement, we can insert multiple rows of data into a table, with the result of a SELECT statement which can get data from one or more tables.

Let's take a few examples to understand how this can be done.

Tables with same columns:

If there is a table Target with columns A, B, and C and another table SOURCE with columns A, B, and C, and we want to insert data from the table Source into the table TARGET, then we can use the following syntax:

INSERT INTO Target(A, B, C)
  SELECT A, B, C
    FROM Source

We can also use the WHERE clause in the SELECT query to get specific row of data. Here is an example for that:

INSERT INTO Target(A, B, C)
  SELECT A, B, C
    FROM Source WHERE A='some value';

Inserting extra values along with SELECT statement result:

If you want to INSERT some constant values in some columns of the table Target along with some values from the columns of table Source, then we do it like this:

INSERT INTO Target (A, B, C) 
SELECT  'some other value', 
        B,
        C
from Source;

In the above query we are inserting a constant string value in the column A of table Target, where as in the other two columns the values are coming from the columns of the Source table.

Again, with this too, we can use the WHERE clause to fetch a specific row of data from the Source table.

Inserting empty or NULL values along with SELECT statement result:

If you want to INSERT some empty or NULL value in some columns of the table Target along with some values from the columns of table Source, then we do it like this:

INSERT INTO Target (A, B, C) 
SELECT  '', 
        NULL,
        C
from Source;

Using the above query, we are inserting an empty value in column A of table Target, and a NULL value into the column B of table Target. We can use WHERE clause here too, if we want.

Conclusion:

Hence using the INSERT...SELECT statement we can easily insert data from one table to another table. If you have any doubt, please feel free to post a comment and ask or head to our discussion forum to ask your question.


IF YOU LIKE IT, THEN SHARE IT

RELATED POSTS