How to Copy Data from One Table to Another in SQL

Copying data from one table to another is a common maintenance task in SQL. There are several ways to accomplish this, depending on your specific needs. In this blog post, we will explore two methods for copying data: copying all elements or selecting specific ones.

Method 1: Copying All Elements

To copy all elements from one table to another, you can use the following SQL statement:

1 2	INSERT INTO some_table SELECT * FROM other_table

This will copy all rows and columns from the other_table to the some_table. However, if the destination table already has data, you may encounter primary key duplication issues.

Method 2: Selecting Specific Rows to Copy

If you only want to copy certain rows from the other_table, you can use the WHERE clause in your SQL statement. For example:

1 2	INSERT INTO some_table SELECT * FROM other_table WHERE list=94

This will copy only the rows from other_table where the list column has a value of 94.

Handling Primary Key Duplication Issues

If the destination table already has data and you want to avoid primary key duplication issues, you can leave the primary key column empty and allow it to auto-fill with its auto-increment value. To do this, you need to select all columns except the primary key in your SQL statement. For instance:

1 2	INSERT INTO some_table (age, name, email) SELECT age, name, email FROM other_table

In the above example, we assume that the id column is the primary key column, and we exclude it from the SELECT statement. This way, the id column in the some_table will be auto-filled with its auto-increment value.

By using these SQL statements, you can easily copy data from one table to another in your database.

tags: [“SQL”, “database”, “data copying”]