While at the surface, the data duplication may seem inefficient; it can sometimes play a very crucial role in instances where you need to have a near exact copy of the same table.
In SQL, we can use various methods and techniques to copy an existing table and have the new table under a new name but with the same data. This can be very useful in certain tasks such as backups, data transformation, temporary data alterations without affecting the main table, and more.
In this guide, we will explore these methods and learn how we can copy a table in SQL databases. Due to the differences on how the various SQL database engines handle the table copying, we will not cover all the methods for each database.
We will try and cover at least one for each database engine, whenever supported, to show you how you can copy a table for each of your supported database engine.
Method 1: Global (Using the CREATE TABLE Statement)
The most common and simplistic method of copying a table is using the CREATE TABLE statement.
Unlike a normal CREATE TABLE statement, we pass a SELECT statement that contains the structure and the data of the source table.
The syntax is as follows:
SELECT * FROM source_table;
This allows us to create a new table with the specified name from the source table.
Take for example the Sakila sample database. Suppose we want to create a similar table as the rentals table.
We can use the previous technique as shown in the following example query:
SELECT * FROM rental;
This should create a new table called “rental_copy” that contains the same structure and data as the rental table.
You can verify by selecting the data from the table as follows:
This should contain the exact data as the rental table.
Method 2: Global (Using the INSERT INTO Statement)
Another method that is globally supported by a wide variety of SQL database is using the INSERT INTO statement.
This technique allows us to copy from one table into another. Unlike the CREATE TABLE and SELECT, this method allows us to selectively fetch the data.
This comes in handy when we need more control in the copying process. We can use the syntax as shown in the following:
SELECT column1, column2, ...
FROM source_table;
In this case, we can specify the columns that we wish to include in the new table without actually fetching everything from the original table.
Take the following query for example:
INTO
rental_copy (rental_id,
rental_date,
return_date)
SELECT
rental_id,
rental_date,
return_date
FROM
rental r;
One drawback of this method is that it may require you to create a similar table with the columns that you wish to include. This can be repetitive and efficient when working with a large dataset.
Method 3: Copy the Table Structure
In other cases, you might come across instances where you are interested in the table structure without needing the data stored in the table.
In such a scenario, you can use the CREATE TABLE statement in conjunction with the LIKE clause as follows:
This should create a new table with the specified name and similar structure as the “source_table” without actually copying the data.
Copying the Tables between Databases
To copy the tables between different databases, we can export the data from the source database and import it into the target database.
This typically involves using the file-based formats like CSV or database-specific tools. You can reference the documentation for you database on how to accomplish this as it may vary depending on the database engine.
Method 4: Using the Linked Servers (SQL Server)
In SQL Server, we can copy the tables between databases using Linked Servers.
Linked Servers allow us to establish a connection to a remote database and query or transfer the data between them.
The syntax is as follows:
SELECT * FROM source_table;
This enables the remote connection and data transfers between remote servers.
Conclusion
In this tutorial, we learned how to use and work with various methods and techniques of copying a table in SQL.
