Add CTAS syntax to SQL Server Standard / Enterprise Edition - by navogel

Status : 


Sign in
to vote
ID 1274700 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 4/24/2015 5:40:47 AM
Access Restriction Public


CREATE TABLE AS (CTAS) is part of ISO 2003 Standard SQL and is supported (with variations) by Microsoft APS and by other SQL DBMSs. CTAS combines CREATE TABLE DDL with a SELECT statement used to insert data to the table, enabling the table to be defined and populated in a single statement.

The nearest equivalent in SQL Server is SELECT INTO but the SELECT INTO syntax does not include any formal DDL so there is no explicit way to predefine datatypes and nullability and no way to specify constraints, partitioning or other table-level features.

Sign in to post a comment.
Posted by Aaron Nelson on 6/17/2016 at 8:31 AM
This capability will be EXTREMELY useful when wanting to create Clustered Columnstore Indexes off of existing [standard-disk based] tables. Furthermore, when wanting to prototype a CCI off of an existing table, this feature would help you cycle through which variation of column combinations works best for your scenario.