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 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)
I also admit that I also didn’t know the word “CTAS” before. Thanks for the sharing! :)
neither did I knew it… thanks…
can it be abbreviated as ‘SIT’ … :) ‘Select Into Table’
or is ‘SIT’ already reserved???
No. It is not already a reserved word
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 )
SELECT * FROM MY_SOURCE_TABLE
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.
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
indeed.. used this as a shortcut for copying a table structure in Oracle… .. :)
CREATE TABLE TABLE2
SELECT * FROM TABLE1 WHERE 1=2
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
Yes, indeed CTAS is a common phrase we use in Oracle to denote Create Table As Select (from another table) !
Thanks, I didnt know CTAS.
I also admit that I also didn’t know the word “CTAS” before.
The same is available with Netezza where we also can create table from some CSV,TXT or excel data..
can i use like following syntax in sql server 2008 or 2005
create table mytemp
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’
What you are using is Oracle syntax. In SQL Server you need to use:
SELECT * INTO mytemp
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.
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 : )
does CTAS work in SQL 2008 ??? It works fine with other DBs like – Oracle,NETEZZA etc
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??
Nice post about CTAS in SQL Server. I have Oracle background and didn’t know about this. Many Thanks!
Can you explain about this statement?
select * from table where 1 = 2
Here, 1 is the number or column no?
It is a static value and not a column name or ordinal position. This will just return no rows
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.
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.
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 ?
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.
I want to know .. if it is possible to enter values into the “TestTable” which is derived from “Person.Contact”
Select * into new_table from old_table
you are right and it is widely known among people using oracle.