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

« SQL SERVER – Denali – Executing Stored Procedure with Result Sets

SQL SERVER – Migration Assistant for Access, MySQL, Oracle, Sybase »

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

April 30, 2011 by pinaldave

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)

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | 10 Comments

10 Responses

on April 30, 2011 at 12:15 pm | Reply Pop

I also admit that I also didn’t know the word “CTAS” before. Thanks for the sharing! :)
on April 30, 2011 at 2:18 pm | Reply Vishal

neither did I knew it… thanks…

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

or is ‘SIT’ already reserved???

:)

Thanks,
Vishal
- on May 2, 2011 at 3:46 pm | Reply madhivanan
  
  No. It is not already a reserved word
on April 30, 2011 at 3:16 pm | Reply Rasel mahmood

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.
on May 9, 2011 at 8:02 am | Reply Jeremiah Peschka

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: http://www.postgresql.org/docs/current/static/sql-selectinto.html#AEN69366
- on May 9, 2011 at 12:21 pm | Reply Vishal
  
  indeed.. used this as a shortcut for copying a table structure in Oracle… .. :)
  CREATE TABLE TABLE2
  AS
  SELECT * FROM TABLE1 WHERE 1=2
on May 13, 2011 at 12:32 pm | Reply Kris

Yes, indeed CTAS is a common phrase we use in Oracle to denote Create Table As Select (from another table) !
on June 1, 2011 at 1:30 pm | Reply nilay

Hi,

Thanks, I didnt know CTAS.

Thanks,

Nilay
on August 21, 2011 at 10:50 pm | Reply 10 effective seo tips

I also admit that I also didn’t know the word “CTAS” before.
on January 16, 2012 at 3:44 pm | Reply Guru

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