SQL SERVER – CTAS – Create Table As SELECT – What is CTAS?

I have been working with the database for many years and I am aware of many common terminologies. Recently I was attending training myself and the instructor used the word ‘CTAS’ in the class. One of the attendees did not know the definition of this abbreviation. From this, I realized that not all of us come from the same background and we all have different levels and areas of expertise.

SQL SERVER - CTAS - Create Table As SELECT - What is CTAS? tables

CTAS stands for ‘Create Table As Select’.

You can read about the same over here in books online Inserting Rows by Using SELECT INTO.

Here is a quick example from my earlier article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE.

This method is used when the table was not created earlier and needs to be created when data from one table needs to be inserted into a newly created table from another table. The new table is created with the same data types as that of the selected columns.

USE AdventureWorks
GO
----Create a new table and insert into table using SELECT, INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

This is a very popular method. So, now we all know one more abbreviation.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Scripts, SQL Server, SQL Table Operation
Previous Post
SQL SERVER – 2012 – Executing Stored Procedure with Result Sets – New
Next Post
SQL SERVER – Migration Assistant for Access, MySQL, Oracle, Sybase

Related Posts

41 Comments. Leave new

  • I also admit that I also didn’t know the word “CTAS” before. Thanks for the sharing! :)

    Reply
  • neither did I knew it… thanks…

    can it be abbreviated as ‘SIT’ … :) ‘Select Into Table’

    or is ‘SIT’ already reserved???

    :)

    Thanks,
    Vishal

    Reply
    • No. It is not already a reserved word

      Reply
    • Actually CTAS is syntactically implemented in MS APS (PDW), it supports syntax as

      CREATE TABLE MY_TABLE
      WITH ( DISTRIBUTION = REPLICATE | HASH(x), COLUMN STORE INDEX , LOCATION = USER_DB )
      AS
      SELECT * FROM MY_SOURCE_TABLE

      Reply
  • Rasel mahmood
    April 30, 2011 3:16 pm

    I want to know more details about this article.This is interesting and nice article also.I hope you will give me clear concept about this.

    Reply
  • Jeremiah Peschka
    May 9, 2011 8:02 am

    It’s referred to as CTAS because many other vendors support the syntax CREATE TABLE AS SELECT * FROM foo WHERE blah = ‘bar’. PostgreSQL even prefers CREATE TABLE AS SELECT over SELECT INTO, even though they are functionally identical: https://www.postgresql.org/docs/current/sql-selectinto.html#AEN69366

    Reply
    • indeed.. used this as a shortcut for copying a table structure in Oracle… .. :)
      CREATE TABLE TABLE2
      AS
      SELECT * FROM TABLE1 WHERE 1=2

      Reply
      • Vishal it would not work, the syntax is for oracle only..
        the same you can do in sql server by following syntax

        Select * into test_bulkload
        from Bulk_Load
        where 1=2

        thanking you

  • Yes, indeed CTAS is a common phrase we use in Oracle to denote Create Table As Select (from another table) !

    Reply
  • Hi,

    Thanks, I didnt know CTAS.

    Thanks,

    Nilay

    Reply
  • 10 effective seo tips
    August 21, 2011 10:50 pm

    I also admit that I also didn’t know the word “CTAS” before.

    Reply
  • The same is available with Netezza where we also can create table from some CSV,TXT or excel data..

    Reply
  • Hi Pinal,
    can i use like following syntax in sql server 2008 or 2005

    create table mytemp
    as
    select * from Bulk_Load where 1=2
    i tried this but, i got one error
    ==============================
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘as’
    ==============================

    Reply
    • Hi Sahhnawaz,

      What you are using is Oracle syntax. In SQL Server you need to use:
      SELECT * INTO mytemp
      FROM Bulk_Load
      WHERE 1=2

      Reply
      • Hi Vishal,

        Thanks for the Query for creating the table without the data from source table. Could you please explain in detail about Where clause? How does it work?

        Appreciate if you could share some links for the same.

        Thanks,
        Subramanian

  • Tanakorn Tavornsasnavong
    March 29, 2012 3:22 pm

    This terminology probably comes from Oracle technique that sometimes CTAS is (a lot) faster than INSERT….SELECT since it bypasses undo mechanism and index update in the table to be inserted. However, you need to create indexes later on : )

    Reply
  • does CTAS work in SQL 2008 ??? It works fine with other DBs like – Oracle,NETEZZA etc

    Reply
  • Hi. I need to use this useful way to create tables, however what happens if I need a column to have IDENTITY functionality?? Is there a way to add it inside the CTAS query or do I have to implement it separately??

    Reply
  • Nice post about CTAS in SQL Server. I have Oracle background and didn’t know about this. Many Thanks!

    Reply
  • Dave,
    Can you explain about this statement?
    select * from table where 1 = 2
    Here, 1 is the number or column no?

    Reply
  • SELECT *
    INTO MYTABLE
    WHERE 1=2

    The “WHERE 1=2” part is a “logical failure”. It simply refers to the numbers 1 and 2, and asks the system to return rows when 1 = 2. Since 1 does not equal 2, the system won’t return rows. But, it does return the field headers, letting you easily copy over a table structure.

    I would probably only use this when I’m cloning a complex table. I could easily clone it, then manually tweak the field data types or what-not as needed. However, if you’re creating, say, a large back-up / recreation script, I wouldn’t rely on this. I would write out the full script to build out the new table.

    Reply
  • Hello,

    We’re in the process of migrating from PostgreSQL to MS SQL Server 2012.

    We used to use CTAS in PostgreSQL. The feature we liked about this is the new table’s columns would NOT get created with NOT NULL constraint (in case it had one).

    In SQL Server 2012, doing “SELECT … INTO dest_table FROM src_table” applies a NOT NULL constraint on dest_table’s columns (if there was such a contraint on src_table’s columns).

    Do you know if there is a way to tell SQL Server 2012 to ignore a potential NOT NULL constraint from src_table ?

    I’m looking for an automated way; I can’t use “ALTER TABLE src_table COLUMN xxx type_of_xxx NULL” for each columns.

    Thanks !

    -C

    Reply
  • Hi , the new table that is created is created in the default filegroup. i.e. it doesn’t create a partitioned table even though source table is partitioned. Any workarounds ?

    Reply
  • Enric Martinez
    April 23, 2013 4:27 pm

    Awesome! I had of course seen this procedure in text books but not under this acronym… and thus forgotten. Under the acronym it sticks in your brain. Fantastic.Thanks Pinal.

    Reply
  • I want to know .. if it is possible to enter values into the “TestTable” which is derived from “Person.Contact”

    Reply
  • Select * into new_table from old_table

    Reply

Leave a Reply