create table from select

I want to create a table from select query result in SQL Server, I tried

but I got an error

Incorrect syntax near the keyword ‘AS’

6 Answers 6

Use following syntax to create new table from old table in SQL server 2008

use SELECT. INTO

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.

Please be careful, MSSQL: «SELECT * INTO NewTable FROM OldTable»

is not always the same as MYSQL: «create table temp AS select..»

I think that there are occasions when this (in MSSQL) does not guarantee that all the fields in the new table are of the same type as the old.

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!

Базы данных

Это учебное пособие Oracle объясняет, как в Oracle/PLSQL использовать CREATE TABLE AS с синтаксисом и примерами.

Описание

Oracle/PLSQL CREATE TABLE AS можно использовать, чтобы создать таблицу из существующей таблицы путем копирования столбцов существующей таблицы.

Важно отметить, что при создании таблицы таким образом, новая таблица будет заполнена записями из существующей таблицы (на основе оператора SELECT).

Создание таблицы — путем копирования всех столбцов из другой таблицы

Синтаксис

Синтаксис CREATE TABLE AS в Oracle PL/SQL, для копирования всех столбцов:

Пример

Рассмотрим пример CREATE TABLE AS, который создаст таблицу путем копирования всех столбцов из другой таблицы.