SQL: CREATE TABLE AS Statement
This SQL tutorial explains how to use the SQL CREATE TABLE AS statement with syntax and examples.
Description
You can also use the SQL CREATE TABLE AS statement to create a table from an existing table by copying the existing table’s columns.
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).
Create Table — By Copying all columns from another table
Syntax
The syntax for the CREATE TABLE AS statement when copying all of the columns in SQL is:
Example
Let’s look at an example that shows how to create a table by copying all columns from another table.
This would create a new table called suppliers that included all columns from the companies table.
If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.
Create Table — By Copying selected columns from another table
Syntax
The syntax for the CREATE TABLE AS statement copying the selected columns is:
Example
Let’s look at an example that shows how to create a table by copying selected columns from another table.
This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.
Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.
Create Table — By Copying selected columns from multiple tables
Syntax
The syntax for the CREATE TABLE AS statement copying columns from multiple tables is:
Example
Let’s look at an example that shows how to create a table by copying selected columns from multiple tables.
This would create a new table called suppliers based on columns from both the companies and categories tables.
Frequently Asked Questions
Question: How can I create a SQL table from another table without copying any values from the old table?
Answer: To do this, the SQL CREATE TABLE syntax is:
This would create a new table called suppliers that included all columns from the companies table, but no data from the companies table.
Acknowledgements: We’d like to thank Daniel W. for providing this solution!
Creating a Table from an Existing Table
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected. When you will create a new table using the existing table, the new table would be populated using the existing values in the old table.
Syntax
The basic syntax for creating a table from another table is as follows −
Here, column1, column2. are the fields of the existing table and the same would be used to create fields of the new table.
Example
Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY −
This would create a new table SALARY which will have the following records −
SQL оператор CREATE TABLE AS
В этом учебном материале вы узнаете, как использовать в SQL оператор CREATE TABLE AS с синтаксисом и примерами.
Описание
Вы также можете использовать SQL оператор CREATE TABLE AS, чтобы создать таблицу из существующей таблицы путем копирования столбцов существующей таблицы.
Важно отметить, что при создании таблицы таким способом новая таблица будет заполняться записями из существующей таблицы (на основе оператора SELECT).
Создать таблицу — путем копирования всех столбцов из другой таблицы
Синтаксис
Синтаксис для оператора CREATE TABLE AS при копировании всех столбцов из другой таблицы в SQL.
Пример
Давайте посмотрим на пример, который показывает, как создать таблицу путем копирования всех столбцов из другой таблицы.
Например.
Create table (structure) from existing table
If you want to copy the entire structure, you need to generate a Create Script of the table. You can use that script to create a new table with the same structure. You can then also dump the data into the new table if you need to.
If you are using Enterprise Manager, just right-click the table and select copy to generate a Create Script.
This is what I use to clone a table structure (columns only).
Copy structure only (copy all the columns)
Copy structure only (copy some columns)
Copy structure with data
If you already have a table with same structure and you just want to copy data then use this
FOR MYSQL:
This will definite work
Its probably also worth mentioning that you can do the following:
Right click the table you want to duplicate > Script Table As > Create To > New Query Editor Window
Then, where is says the name of the table you just right clicked in the script that has been generated, change the name to what ever you want your new table to be called and click Execute
I use the following stored proc for copying a table’s schema, including PK, indexes, partition status. It’s not very swift, but seems to do the job. I I welcome any ideas how to speed it up: