Create Table As in SQL Server

Greetings, Dev! If you are a database developer, then you must have heard about the create table as statement in SQL Server. It is a powerful tool that can help you in many ways. In this article, we will cover everything you need to know about creating tables using the create table as statement. So, let’s dive in!

Introduction

The create table as statement is used to create a new table and populate it with data from an existing table or view. This statement is particularly useful when you want to create a new table with some or all of the data from an existing table.

With create table as statement, you can create a table with only the columns you want or rename columns of the original table. You can also apply a condition to select only a subset of the original table’s data.

Syntax

The syntax for create table as statement in SQL Server is as follows:

CREATE TABLE AS statement syntax
CREATE TABLE new_table_name [column1, column2…columnN]AS SELECT column1, column2…columnN FROM existing_table WHERE condition;

The create table as statement consists of the following elements:

• new_table_name: The name of the new table you want to create.
• column1, column2…columnN: The columns you want to include in the new table. If you don’t specify any columns, all columns from the existing table will be included.
• existing_table: The name of the existing table or view.
• condition: The condition to select a subset of data from the existing table.

Examples

Let’s take a look at some examples of using the create table as statement in SQL Server.

Example 1: Creating a table with all columns from an existing table

To create a new table with all the columns from an existing table, you can use the following syntax:

CREATE TABLE AS statement example 1
CREATE TABLE new_table AS SELECT * FROM existing_table;

In this example, we are creating a new table called new_table with all the columns from an existing table called existing_table.

Example 2: Creating a table with selected columns from an existing table

If you want to create a new table with only selected columns from an existing table, you can specify the column names in the create table as statement, as shown in the following example:

CREATE TABLE AS statement example 2
CREATE TABLE new_table (column1, column2…columnN) AS SELECT column1, column2…columnN FROM existing_table;

In this example, we are creating a new table called new_table with only selected columns column1, column2…columnN from an existing table called existing_table.

Example 3: Creating a table with renamed columns from an existing table

You can also create a new table with renamed columns from an existing table, as shown in the following example:

CREATE TABLE AS statement example 3
CREATE TABLE new_table (new_column1, new_column2…new_columnN) AS SELECT old_column1 AS new_column1, old_column2 AS new_column2…old_columnN AS new_columnN FROM existing_table;

In this example, we are creating a new table called new_table with renamed columns new_column1, new_column2…new_columnN from an existing table called existing_table.

Example 4: Creating a table with selected rows from an existing table

You can also create a new table with selected rows from an existing table by applying a condition in the create table as statement, as shown in the following example:

CREATE TABLE AS statement example 4
CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition;

In this example, we are creating a new table called new_table with all the columns from an existing table called existing_table, but only for rows that meet the condition specified in the WHERE clause.

FAQ

Q: Can I create a table with indices using the create table as statement?

A: Yes, you can create a table with indices using the create table as statement. However, you need to create the indices separately after creating the table.

Q: Can I create a table with data from multiple tables using the create table as statement?

A: No, you cannot create a table with data from multiple tables using the create table as statement. You need to use a join statement to combine data from multiple tables and then use the create table as statement to create a new table with the combined data.

Q: Can I create a temporary table using the create table as statement?

A: Yes, you can create a temporary table using the create table as statement. To do this, simply prefix the table name with a hash (#) sign.

Conclusion

The create table as statement is a powerful tool that can help you create new tables with data from existing tables or views. With the ability to select columns, rename columns, and apply conditions, you can create highly customized tables to meet your needs. We hope this article has been helpful in understanding how to use the create table as statement in SQL Server.