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.

CTAS stands for ‘Create Table As Select’.

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

Here is 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 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 same data types as that of the selected columns.

USE AdventureWorks
GO
----Create 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 (http://blog.SQLAuthority.com)

About these ads

34 thoughts on “SQL SERVER – CTAS – Create Table As SELECT – What is CTAS?

  1. neither did I knew it… thanks…

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

    or is ‘SIT’ already reserved???

    :)

    Thanks,
    Vishal

      • 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

  2. 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’
    ==============================

      • 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

  3. 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 : )

  4. 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??

  5. 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.

  6. 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

  7. 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 ?

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

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

  10. It works sqlserver 2008R2 , usefull !

    select * into Table D01BIS from table D01

    In DB2
    create table D01BIS as (select * from D01) WITH DATA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s