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:
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!