Duplicate Tables

SELECT * INTO NewTable FROM OldTable

Absolutely brilliant snippet of SQL. NewTable doesn’t even have to exsit – it copies the schema from OldTable.

Also, as suggested here, you can just copy the schema (without contents) by doing:

SELECT TOP 0 * INTO NewTable FROM OldTable
From what I can make out, this functionality was added in SQL Server 2005. It’s a SQL standard too so is supported by other SQL systems too.
Do not use this followed by a TRUNCATE to rename a table! SP_RENAME is the non-standard T-SQL method of renaming tables.
Posted in SQL