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.

I this tutorial, we will learn how to combine these two statements for copying the table data from one table to another.

The combination is simple; the SELECT statement retrieves the records from one or more tables while 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 SELECT command) to the target table (specified in the INSERT INTO).

However, certain things to keep in mind as using this combination for copying data:

• The data types of the target table must match to 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 you 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 source to 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, 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 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 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 for 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:

The columns with NULL constraint are displaying the values as NULLs.

The query for copying data from one database table to other

In all above examples, both tables were existing in the same database with the same username, so we did not require specifically mentioning 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 table as source and target as in 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:

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 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.