Tips ‘N’ Tricks – T-SQL – Quickest way to Copy a Table Structure
Taking a backup of a Table’s data is a very common task which all of us perform at some point for various reasons like testing, data backup etc. On similar lines, sometime we may just need to copy the table structure without the data. In this tips let’s see how to quickly copy a table both with or without data.
We will be achieving both the above requirements – Copying table with data and without data (Structure only).
Copy Table with Data
SELECT * INTO dbo.CopyOfEmployeeWithData
The above query will create a table named CopyOfEmployeeWithData under the dbo schema. The structure of this table is based on Employee table from HumanResources schema. And copies the data from HumanResources.Employee table into dbo.CopyOfEmployeeWithData table.
Copy Table without Data (Copy Table Structure only)
SELECT * INTO dbo.CopyOfEmployeeWithoutData
WHERE 1 = 2
The above query will create a table named CopyOfEmployeeWithoutData under the dbo schema. The structure of this table is based on Employee table from HumanResources schema. This query will only create the table structure and does not copy the data. Copying of data into the new table is restricted using the hard-coded WHERE Clause (note that, instead of 1 & 2, any other numbers, characters etc. can be used but we need to make sure that the WHERE Clause is not satisfied).
The newly created table structure using either of the above approaches has the following major limitations:
Keys are not copied to the newly created table (Primary Keys, Foreign Keys, and Alternate Keys etc.)
Indexes are not copied to the newly created table
Constraints are not copied to the newly created table
However, the above approaches can be used for quick backup of data or backup of table structure (with just columns, data types, etc. keeping in mind the above restrictions).
Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.
Posted on May 14, 2012, in SQL Server, T-SQL, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged Backup, Microsoft SQL Server, MSBI, SQL Server, T-SQL, Tips and Tricks. Bookmark the permalink. 3 Comments.