49

I want to create a table based on the definition of another table.

I'm coming from oracle and I'd normally do this:

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

I can't seem to be able to do this in SQL Server 2008.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
oliverdejohnson
  • 1,512
  • 2
  • 14
  • 22

2 Answers2

122

There is no such syntax in SQL Server, though CREATE TABLE AS ... SELECT does exist in PDW. In SQL Server you can use this query to create an empty table:

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

(If you want to make a copy of the table including all of the data, then leave out the WHERE clause.)

Note that this creates the same column structure (including an IDENTITY column if one exists) but it does not copy any indexes, constraints, triggers, etc.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
20
select * into newtable from oldtable
Sonam
  • 3,406
  • 1
  • 12
  • 24
  • this is a ms sql server question – Jonathan Dec 03 '14 at 19:24
  • 8
    @Jonathan And what is the problem? – John Sep 05 '15 at 08:16
  • Does this preserve the types of the columns precisely or does it "compact" the data? For example, choosing `varchar(5)` instead of `varchar(20)` because none of the values were longer than 5 characters? – Leonid Aug 06 '18 at 05:39
  • 1
    @Leonid I haven't found any written source, but it looks like it preserves the structure exactly: `CREATE TABLE #origin (how_long varchar(50)) SELECT * INTO #new FROM #origin WHERE 1 = 0 EXEC tempdb..sp_help '#new'` – Doctor Rudolf Sep 25 '18 at 07:18
  • 1
    This is not a corrrect answer, since CREATE...LIKE only copies schema, and no data is copied, but this query copies data as well – Nikita Malyavin Oct 08 '20 at 06:29