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