Copying Data from one Table to Another in SQL

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.

The combination is simple; the SELECT statement retrieves the records from one or more tables while the INSERT INTO command enters those records into the target table.

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;

SQL INSERT SELECT

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:

SQL INSERT SELECT WHERE

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.

Note that, if you omit the NOT NULL column it will generate an error.

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:

SQL INSERT SELECT column

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:

SQL INSERT DBs

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.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!