We have learned that the INSERT statement is used to enter new records in tables.
We also know that the SQL SELECT statement fetches the record-sets from one or more database tables.
In this tutorial, we will learn how to combine these two statements to copy the table data from one table to another.
A simple example of using these two statements:
INSERT INTO target_table SELECT * FROM source_table;
This single SQL statement will copy complete data from the source table (in the SELECT command) to the target table (specified in the INSERT INTO).
However, there are certain things to keep in mind when using this combination for copying data:
- The target table’s data types must match the data types of the source table.
- If you use the SELECT statement without the WHERE clause, complete table data is copied to the target table.
- You may also specify a few columns to copy data from one table to another.
I will show examples of copying complete table data, only specific columns, and copying only specific rows from one table to another in the next section.
An example of copying complete table data
For showing the INSERT INTO & SELECT statements demo, I am going to use sto_employees table as the source table and sto_employees_sub as the target table.
In this example, we will completely copy the table data from the source to the target table; so no WHERE clause is used in the SELECT statement:
SELECT * FROM sto_employees_sub; INSERT INTO sto_employees_sub SELECT * FROM sto_employees; SELECT * FROM sto_employees_sub;
In the graphic above, you can see the first query returned no record from the table. This is followed by using INSERT INTO / SELECT statements for copying the data.
After that, another SELECT query is executed to retrieve data from sto_employees_sub table and it shows all records copied from the source table.
The example of copying specific rows
In this example, only those records are copied to the sto_employees_sub table for what emp_salary is greater than or equal to 4000 from the sto_employees table.
Before executing this query, I emptied the table populated by the above query:
The INSERT INTO/SELECT query for copying specific rows:
INSERT INTO sto_employees_sub SELECT * FROM sto_employees WHERE emp_salary >= 4000;
The result-set:
You can see the salary column where its value is greater than or equal to 4000.
The example of INSERT INTO/SELECT with specific columns
You may also insert rows for specific columns from one table to another.
For that, enclose the column names in the parenthesis in the INSERT INTO statement and use the corresponding column names in the SELECT statement.
For the demo, I again removed all records from the target table and executed this query with a few column names:
INSERT INTO sto_employees_sub (id, emp_name, emp_age, emp_salary) SELECT id, emp_name, emp_age, emp_salary FROM sto_employees;
The target table retrieved before and after executing this query and see the result:
The columns with NULL constraint display the values as NULLs.
The query for copying data from one database table to other
In all the above examples, both tables existed in the same database with the same username, so we did not require specifically the DB and user names.
The data copy generally requires from one database table to the other in real-time scenarios.
The following query shows how to copy data from one database (store_db) to another database (test_db) in the same database server:
As you should be familiar with the above examples, I am using the same two tables as source and target as in the above examples:
INSERT INTO [test_db].[dbo].[sto_employees_sub] SELECT * FROM [store_db].[dbo].[sto_employees] WHERE emp_salary BETWEEN 3500 AND 5500;
Where test_db and store_db are the database names while dbo is the username.
The result-sets before and after executing this query for the [test_db].[dbo].[sto_employees_sub] table:
For copying data from one database server to another by using INSERT INTO with SELECT e.g. local database to the remote database server or vice versa, the example query may look like this:
INSERT INTO [database-instance].[test_db].[dbo].[sto_employees_sub] SELECT * FROM [database-instance] [store_db].[dbo].[sto_employees]
For instance, if the DB server name for local installation is “WELCOME \MSSQLTEST” and the remote DB Server name is REM-PC then the query should be:
INSERT INTO [WELCOME \MSSQLTEST].[test_db].[dbo].[sto_employees_sub] SELECT * FROM [REM-PC] [store_db].[dbo].[sto_employees]
That should copy the data from the remote database server to the local server.