How to copy Table Structure Without Data in SQL Server

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 14905 red flag

find the trick to copy table structure from another existing table.


In some situations we need to create table from another table for many reasons, for example, We need to make some changes in the table`s schema, and we want to keep the original schema as a backup or we just want a look a like table, where we can put some data as backup in future etc.
Of course to copy a table`s structure you must have an existing table, so let us create a table and put some data.

Create Table BaseTable (Id Int , Name Varchar(50), CreatedOn Datetime )
Insert into BaseTable Values (1 , 'Neeraj Prasad Sharma' , GETDATE())
Insert into BaseTable Values (2 , 'Vikas Kumar' , GETDATE())
Insert into BaseTable Values (2 , 'Mark Wizz' , GETDATE())

We have a table with some data, now copy columns from base table using INTO syntax:

Select * INTO CopyTable From BaseTable where 1=2

Note the where condition, we just pass a false statement, can pass any false statement here like 'a'='b' or 2=0 etc.

Now let`s check the existence of our newly created copy table.

Select * From CopyTable

Select INTO command will copy table from some other table with its Datatype, but it  doesn`t copy any Constraint (primary key, foreign key etc.)  except the Not Null constraint.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here:

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)