How do I create a table based on another table

Question

On the basis of the definition of another table, I want to construct a new table.

Coming from Oracle, I would typically do the following:

 CREATE TABLE schema.newtable AS SELECT * FROM schema.oldtable;

I can't seem to be able to do this in SQL Server 2008. Can someone please help me with this?

Answer 1

Although CREATE TABLE AS... SELECT does exist in PDW, such syntax does not exist in SQL Server. You can use the following query in SQL Server to create a blank table:

SELECT * INTO schema.newtable FROM schema.oldtable WHERE 1 = 0;

Note that this does not copy any indexes, constraints, triggers, or other data structures; instead, it constructs the same column structure, including any existing IDENTITY columns. 

I hope this helps you.