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)
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
This is not the create from select method. The whole point of CTAS is to avoid creating the target table from scratch.
Please ignore this thread.
Please ignore previous comment. Sorry.
what is the equivalent function for monotonic () in MS SQL
what is the equivalent function for monotonic in MS SQL
CTAS is more commonly used by the Oracle folks.
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
Thanks kselvakumar but I am not seeing relation with blog post and comment. Did i miss something?
I haven’t known it too, I always use the full form. I wonder if everything in the SQL Server has got own abbreviation :)
amazing and helping
Thanks @Anand.
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 …