How to Copy an SQL Table Using MySQL, MS-SQL or other SQL Formats.

How to Copy an SQL Table Using MySQL, MS-SQL or other SQL Formats.
Page content

Copying an SQL table

You may need to copy a database table for various different reasons. If you are a database administrator (DBA), you may want to copy a table so that you can edit and play around with its data. Also, you may want to copy a table for backup and restore or testing purposes, without endangering the original data.

The process of copying an SQL table is very easy. If you are familiar with basic SQL, you should have no problem learning how to copy an SQL table. We can copy all or specific data from a table. We also have an option of creating a new table, then copying or coping data to an existing table. In this article, we’ll learn different techniques on how to do this.

How to copy an SQL table into another table in the same database:

Select * into from

Example:

Select * customer_backup from customer;

If you don’t want to copy the entire table, you can select columns you want to copy into the destination table. Here’s how:

Select column1, column4 into from

Example:

Select customerID, customerfName, customerAdd into customer_backup from customer;

Use this method to copy a table across two databases:

Select * into from

Example:

Select * into yourdatabase.dbo.customer_backup from yourdatabase.dbo.employee;

If you only want to copy the structure of the source table:

Select * into f_rom where 1=2_

Example:

Select * into customer_backup from customer where 1=2;

Creating a new table and copying

The above examples demonstrate how to copy SQL table contents to existing tables. If the destination table doesn’t exist and you run any of the above SQL statements, the table won’t be copied. Therefore, you must create a table before you can copy any content to it. This is how you create a new SQL table:

The SQL syntax for creating a new table is:

CREATE TABLE “new_table_name” (

“column 1” “data_type”, “column 2” “data_type”

“column 3” “data_type”

“column 4” “data_type”

)

So, if I were to create a new customer table I would type in:

CREATE TABLE customer (

Cust_ID INT NOT NULL PRIMARY KEY,

First_Name VARCHAR(30),

Last_Name VARCHAR(30),

Address VARCHAR(50)

)

Now that you know how to copy and create a table using SQL, here is another technique that allows you to create a new table and populate it with a single SQL command:

Create table copy_table select * from originaltable;

These are the easy ways to learn how to copy an SQL table. Whether you are using MySQL, MS-SQL or any other flavor of SQL, the syntax of SQL remains same.

Now that you’ve learned how to copy an SQL table, you can always copy a table before testing or experimenting with the original data. By doing so, you will be confident that your original table is safe.