Create Table As Select - CTAS

Create Table As Select aka CTAS. CTAS quickly lets your create a new table modeled after a table that already exist in your database. I use CTAS when I need to quickly run some test command or to set up a lab in order to run some test cases. DBA and developer use CTAS to make a backup of a table before they alter the production table or  modify data.

You can copy it back(rollback the change!) just in case the change didn't go as planned. CTAS will let you copy table structure along with the data or just table structure only. The choice is yours! You will just need to be a little creative with CTAS to achieve what your want to accomplish. I will run some CTAS statements below to demonstrate that table creation.

Oracle Syntax:

--Copies table along with data 
CREATE TABLE newtable AS 
SELECT * 
FROM   existing_table; 

--Verify that table and data  both exist
SELECT Count(*) 
FROM   existing_table; 

--Create table structure only, no data
CREATE TABLE newtable AS 
SELECT * 
FROM   existingtable 
WHERE  1 = 2; 

--Verify table exist
Describe existing_table;

SQL Server Syntax:

 
----Create table along with data 

SELECT firstname, 
       lastname 
INTO   testtable      --New table 
FROM   person.contact -- existing table 
 
----Verify that Data in TestTable 
SELECT firstname, 
       lastname 
FROM   testtable; 

--Table only
SELECT firstname, 
       lastname 
INTO   testtable      --New table 
FROM   person.contact -- existing table 
WHERE  1=2; 

--Verify table structure 
SP_HELP testtable; 
--similar to desc command of Oracle

Limitations & Restrictions: CTAS copies existing table structure along with data but it does NOT copy the table constraints. You will need to add/alter the table manually, therefore Iit is good idea to verify the constraints and table structures.

CTAS is not suitable for moving a test database's table to prod database, You know the reason why? I would run the actual script in production database not just CTAS. The lab exercise was done on Oracle 11g db, and this MAY or MAY NOT apply to SQL Server db, I will leave that for you guy to find out!