Tips and Tricks to Create New Table from Existing Table

Here I want to discuss how to create new table from existing table by using SQL statement In real-time environment when SQL developers taking backup and/or making some change on the existing table, they might need to keep the original records in a save place so in this case it is necessary to create copy of existing table.

Today we look at following topic:

  • Creating new table from existing table with copy of all records
  • Creating new table with specific columns from existing table along with copy of records
  • Creating new table from existing table without records [only structure]
  • Copy data from one table to another table.

Creating new table from existing table

In this case, we will create new table with copy of all rows and columns from the old table along with data.

Syntax:

SELECT */<LIST OF COL.> INTO  <NEW_TABLE_NAME>  FROM <OLD_TABLE_NAME>

First, let me create a sample table, which I named COURSE. COURSE contains CID, CNAME, and CFEE as shown below:

CREATING:

Creating table by using SQL statement in query editor with the help of CREATE command.

INSERTING:

By using INSERT command, we can insert values into a table as follows:

Display Content:

With the help of SELECT command, we can display content of a table as shown below:

Now we create a new table from this table, which has exactly same as old one with rows and columns as follows:

It will create table, which is a copy of COURSE table along with data content.

So now, if we make some change on the old table, we have a backup, which keeps the original values of our data. In addition, we can get back it if you people needed.

Creating new table with specific columns from existing table

Now let us create new table from existing table with selected columns along with data. In order to create a table with specific columns we need to list out the columns that we want to be present in a new table. Following query is doing this job for us as shown below:

This above query will create a table named COURSE2, which contains only two columns [C_ID, C_FEE] with data content as follows:

Creating new table from existing table without records [only structure]

Everything is same as before but we must use flash condition. Flash condition is nothing but a condition in where clause, which never becomes true. Due to flash condition, it will create a table with columns only not data content [rows]. You people need in many cases only schema of a table not records to reduce workload of creating another table, which has same structure of existing one.

Syntax

SELECT* /<LIST OF COL.> INTO <NEW_TABLE_NAME> FROM <OLD_TABLE_NAME> WHERE <FLASH CONDITION>

By using same table let me create structure of table COURSE as follows:

This above query will create a table, which contains all columns of COURSE table without data content as:

In addition, we can create empty table with specific columns from existing table with help of FLASH CONDITION, as follows:

This table contains only two columns in which we specified and we can display.

Copy data from one table to another table.

As we know in my cases, you people need to copy data from one to another. However, before copying from one to another table we should maintain the following rules:

  1. Both tables should contain same number of columns
  2. Order of the columns and data types of both tables should be the same.

Here we require a combination of two commands INERT and SELECT.

Syntax:

INSERT [INTO] <DEST.TABLE> SELECT * FROM <SRC.TABLE>

Example

We create DummyCourse table, which contains same schema of COURSE, table.

Inserting values:

Display Content:

Thank you for your patient. To get more articles please share and leave comment.

Follow us:

Leave a Reply

Your email address will not be published. Required fields are marked *