Creating Table From Existing Table in Hive, Impala, SQL Server, Oracle SQL*Plus, PostgreSQL and MySQL

In some scenarios we may need to create a table based on the existing ones. Sometimes we may need the table along with the data and sometimes we may need only the table structure. Most of the RDBMSs, Apache Hive and Cloudera's Impala supports CREATE TABLE.. AS and CREATE TABLE.. LIKE. 

In both the scenarios, we do not specify the columns at all; the column names and types are derived from the source table, query, or data file.

Use of CREATE TABLE ... LIKE is to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table. On the other hand, CREATE TABLE ... AS is to create the table based on the columns specified in the SELECT statement. It helps in fetch the only columns that required. However, the column definitions will be as same as in source table.

Let's see in which RDBMSs we have these functionalities. 

As stated in the above picture, most of the relational applications do support 'Create Table..As' and 'Create Table..Like' except Microsoft SQL Server. However there is another approach available in SQL Server to accomplish the same task. Before we discuss about SQL Server functionality we will look into the Create table statements which will work in MySQL, Oracle SQL*Plus, PostgreSQL, Hive and Impala.

CREATE TABLE <newTableName> AS <SelectStatement>

CREATE TABLE <newTableName> LIKE <ExistingTableName>

Let's see some examples:

CREATE TABLE EmployeeData AS SELECT EmpID, Ename, Sal FROM Emp;

As you see, we are selecting only required columns and not fetching the DeptNo from Emp table. This will create the table EmployeeData with Three columns along with the data. There is a workaround to create the table with the desired columns definitions without importing the data too.

CREATE TABLE EmployeeData AS SELECT EmpID, Ename, Sal FROM Emp WHERE 1=2;

Since 1=2 is false, the data import will not happen but the table will be created with the specified columns.

CREATE TABLE..LIKE will not give the scope to select the few columns from the existing table. It will copy the source table's structure. Let's see an example -

CREATE TABLE EmployeeData LIKE Emp;

If you are working with PostgreSQL and if you are getting a syntax error then use 

CREATE TABLE EmployeeData (like Emp); 

Now let's check how this can be accomplished using Microsoft SQL Server.

SELECT * INTO EmployeeData FROM Emp;

This statement will copy the Emp table's structure and data into EmployeeData table. Here also we can fetch the columns based on requirement; See the below example to understand.

SELECT EmpID, Ename, Sal INTO EmployeeData FROM Emp;

 If your requirement is to create table without importing the data, you can use a where clause that fails.

SELECT EmpID, Ename, Sal INTO EmployeeData FROM Emp WHERE 1=2

Hope you find this article helpful.