Pin Me

How to Copy or Create a New SQL Table

written by: S. R. Obbayi•edited by: Michele McDonough•updated: 5/25/2010

When you copy a table for testing, backup or other reasons, you can rest assured that your original data is safe. Copying an SQL table is a very simple task. If you have even the slightest knowledge of SQL, then you can easily copy a table. Here are some techniques on how to copy an SQL table.

  • slide 1 of 2

    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.sql-statement-copy 

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

    Select * into <destination table> from <source table>

    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 <destination table> from <source table>

    Example:

    Select customerID, customerfName, customerAdd into customer_backup from customer;

    Use this method to copy a table across two databases:

    Select * into <destination dbname.dbo.destination table> from <source dbname.dbo.source table>

    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 <destination table> from <source table> where 1=2

    Example:

    Select * into customer_backup from customer where 1=2;

  • slide 2 of 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.