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
FROM HumanResources.Employee

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
FROM HumanResources.Employee
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).

Referencehttps://dattatreysindol.com/2010/02/06/copy-table-structure-with-or-without-data-in-sql-server/

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.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on May 14, 2012, in SQL Server, T-SQL, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged , , , , , . Bookmark the permalink. 3 Comments.

  1. “… WHERE 1=2″… wouldn’t that cause SQL to look at every row? Would “SELECT TOP 0 *…” be faster?

  2. Hi Greg,

    Thank you for your comment.
    If you look at the WHERE clause, it is a constant being compared with another constant and there are no columns involved in there. As a result, there will not be any row level scans as such.
    To confirm this, I just generated an execution plan and found that both the approaches (WHERE 1=2 and SELECT TOP 0) have equal cost.

  1. Pingback: Tips ‘N’ Tricks – Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills « Datta's Ramblings on Business Intelligence 'N' Life

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: