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.
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
INSERT INTO Target(A, B, C) SELECT A, B, C FROM Source WHERE A='some value';
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.
Also Read: DBMS & SQL Tutorial
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.
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.