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.
How to SELECT data from one Table and INSERT into Another
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 the 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 the WHERE clause here too, if we want.
Conclusion
Efficiently migrating data between tables is a crucial skill in database management. By harnessing the power of the SELECT and INSERT statements, you can seamlessly transfer data from one table to another, opening up possibilities for data consolidation, synchronization, and population.
As you dive deeper into the world of data migration, remember the importance of understanding your data models, utilizing proper SQL syntax, and considering performance optimization techniques.
Hence using the INSERT...SELECT statement we can easily insert data from one table to another table. If you have any doubts, please feel free to post a comment and ask or head to our discussion forum to ask your question.
Frequently Asked Questions(FAQs)
1. Can I SELECT data from one table and INSERT it into another in a single SQL statement?
Yes, you can accomplish this task by using the INSERT INTO SELECT statement in SQL. It allows you to select data from one table and directly insert it into another table in a single statement, streamlining the data migration process.
2. What is the difference between INSERT INTO SELECT and INSERT INTO VALUES in SQL?
The INSERT INTO VALUES statement is used to insert explicit values into a table, whereas the INSERT INTO SELECT statement allows you to select data from one or more tables and insert it into another table. INSERT INTO SELECT offers more flexibility for data migration and manipulation.
3. Are there any limitations or considerations when using INSERT INTO SELECT in SQL? A
When using INSERT INTO SELECT, ensure that the column names and data types in the target table match the selected columns from the source table. It's also crucial to consider the performance implications, such as indexing, transaction size, and data integrity, especially when dealing with large datasets.
4. Can I apply filters or transformations while selecting data for insertion?
Absolutely! You can apply various filtering conditions, sorting joins, and transformations within the SELECT statement to tailor the data being inserted. This enables you to extract specific subsets of data or perform calculations during the migration process.
5. Can I select data from multiple tables and insert it into one table using INSERT INTO SELECT?
Yes, you can select data from multiple tables using JOINs or subqueries within the SELECT statement and insert the combined result into a single table using INSERT INTO SELECT. This technique is particularly useful for consolidating data from multiple sources or performing complex data migrations.
You may also like:
