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)

, ,
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

  • It works sqlserver 2008R2 , usefull !

    select * into Table D01BIS from table D01

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

    Reply
  • This is not the create from select method. The whole point of CTAS is to avoid creating the target table from scratch.

    Reply
  • Please ignore previous comment. Sorry.

    Reply
  • what is the equivalent function for monotonic () in MS SQL

    Reply
  • what is the equivalent function for monotonic in MS SQL

    Reply
  • CTAS is more commonly used by the Oracle folks.

    Reply
  • Hey Pinal

    Post the Topic related to this , it will be helpfull for many others

    BEGIN TRAN

    USE master

    IF OBJECT_ID ( ‘dbo.SpatialTable’, ‘U’ ) IS NOT NULL
    DROP TABLE dbo.SpatialTable;
    GO

    CREATE TABLE SpatialTable
    ( id int IDENTITY (1,1),
    GeomCol1 geometry,
    GeomCol2 AS GeomCol1.STAsText() );
    GO

    INSERT INTO SpatialTable (GeomCol1)
    VALUES (geometry::STGeomFromText(‘LINESTRING (100 100, 20 180, 180 180)’, 0));

    INSERT INTO SpatialTable (GeomCol1)
    VALUES (geometry::STGeomFromText(‘POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))’, 0));
    GO

    COMMIT

    ROLLBACK

    SELECT * FROM spatialtable

    Reply
    • Thanks kselvakumar but I am not seeing relation with blog post and comment. Did i miss something?

      Reply
  • bezpieczne haslo
    August 31, 2016 6:46 pm

    I haven’t known it too, I always use the full form. I wonder if everything in the SQL Server has got own abbreviation :)

    Reply
  • amazing and helping

    Reply
  • Robert Skinner
    May 14, 2020 4:33 am

    The point of CTAS in its native oracle and modified SQL variant is to insert data and mimic schema without any log overhead, in my opinion and experience. If you instead create and insert, you have missed an opportunity to avoid clipboarding the schema and doing the create. If you then insert, the system must keep track the inserts to log for commitment. But Creating As has a meaningless previous state that has no purpose for rollback so no logs. I did warehousing overnight by CTAS from views just to not create logs. I would drop and create table_1 while yesterdata lived in table_0 and the synonym for the table was set to table_0. Then i would index the table and toggle the synonym. During the day there was change capture available as two days of data were present. No logging whatsoever. But i needed to come back here to recall the SQL method which isn’t as elegant as Oracle Create Table As …

    Reply

Leave a Reply

Menu