SQL: CREATE a table from another table
来源:互联网 发布:大学生贷款的软件 编辑:程序博客网 时间:2024/05/16 02:17
You can also 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).
Syntax #1 - Copying all columns from another table
The basic syntax is:
CREATE TABLE new_table
AS (SELECT * FROM old_table);
For Example:
CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);
This would create a new table called suppliers that included all columns from thecompanies table.
If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.
Syntax #2 - Copying selected columns from another table
The basic syntax is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n FROM old_table);
For Example:
CREATE TABLE suppliers
AS (SELECT id, address, city, state, zip
FROM companies
WHERE id > 1000);
This would create a new table called suppliers, but the new table would only include the specified columns from thecompanies 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.
Syntax #3 - Copying selected columns from multiple tables
The basic syntax is:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);
For Example:
CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000);
This would create a new table called suppliers based on columns from both thecompanies and categories tables.
Frequently Asked Questions
Question: How can I create a table from another table without copying any values from the old table?
Answer: To do this, the basic syntax is:
CREATE TABLE new_table
AS (SELECT * FROM old_table WHERE 1=2);
For Example:
CREATE TABLE suppliers
AS (SELECT * FROM companies WHERE 1=2);
This would create a new table called suppliers that included all columns from thecompanies table, but no data from the companies table.
Acknowledgements: We'd like to thank Daniel W. for providing this solution!
http://www.techonthenet.com/sql/tables/create_table2.php
- SQL: CREATE a table from another table
- sql cursor update a table by another table
- sql server 2008 copy data from one table to another
- sql--Delete repeated items from a table
- SQL:create table简介
- CREATE TABLE (Transact-SQL)
- SQL CREATE TABLE 语句
- create table xx.sql
- SQL CREATE TABLE
- SQL CREATE TABLE 语句
- SQL CREATE TABLE 语句
- SQL入门(Create Table)
- postgresql sql create table
- SQL CREATE TABLE 语句
- sql create table index
- SQL CREATE TABLE 语句
- SQL语句CREATE TABLE
- SQL CREATE TABLE 语句
- Oracle 物化视图
- 安装Android SDK
- 关于C6000DSP的堆与栈的解释zt
- Linux Shell 基础
- 软件分享 AirPlayer
- SQL: CREATE a table from another table
- windows下配置svn服务器
- 你好世界!
- Android中调用外部地图程序
- SQL Server sys.objects
- 两个UIPickerView控件间的数据依赖
- java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 解决方法
- JavaScript类和继承:this属性
- Oracle 10g Scheduler 特性