SQL SERVER: Copy only structure into a new table without data from existing table.

Introduction: In this article I am going to share SQL trick to create a new empty table from existing table by copying table schema/structure i.e. all columns with their data types but no data. 

In previous articles i explained Insert into select from statement to copy data from one table to another and Select into statement to copy data from one table and insert into new table and Get first, last date and total number of days in month and Difference between temporary table and table variable in sql server and Example to use Cross join or Cartesian join in sql server

Description: Some time we require creating a duplicate table same as the existing table with all the columns but without data. It can be done with just a single line query. I have mentioned two ways to get this done.

Implementation: Let’s create a sample table and perform the desired operation.

 --Create a table(Source)

GO

CREATE TABLE tbBooks

(

    BookId        INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

    BookName   VARCHAR(100),

    Author         VARCHAR(100),

    Publisher      VARCHAR(100),

    BookPrice     DECIMAL(10,2)

)

--Add some dummy data into the table

GO

INSERT INTO tbBooks VALUES

('Asp.Net','Ajay','Rozy Publication',1200),

('C#.Net','Sahil','Jai Publication',1000),

('VB.Net','Nancy','Rozy Publication',970),

('MVC','Sahil','Amar Publication',1480),

('JAVA','Supreet','Sam Publication',850),

('PHP','Parvesh','Maya Publication',800)

 --Check inserted data

SELECT * FROM tbBooks 

BookId	BookName	Author	Publisher	BookPrice
1	Asp.Net	Ajay	Rozy Publication	1200.00
2	C#.Net	Sahil	Jai Publication	1000.00
3	VB.Net	Nancy	Rozy Publication	970.00
4	MVC	Sahil	Amar Publication	1480.00
5	JAVA	Supreet	Sam Publication	850.00
6	PHP	Parvesh	Maya Publication	800.00

First Way to Copy only structure of source table and create a new empty table.

SELECT * INTO tbBookList FROM tbBooks WHERE 1=0

--Check newly created table 

SELECT * FROM tbBookList

Result:

BookId

BookName

Author

Publisher

BookPrice

Note:  A new table with the same columns and data types as were in source table will be created. No data will be inserted in this table since 1 is never equal to 0.

Second Way to Copy only structure of source table and create a new empty table.

SELECT TOP 0 * INTO tbBookData FROM tbBooks

--Check newly created table 

SELECT * FROM tbBookData

Result:

BookId

BookName

Author

Publisher

BookPrice

Point to remember: Note that both of the above queries only copies the columns along with their data type but these will not copy indexes, keys, constraints etc of the source table to target table.

So you have to manually generate script of the source table by right clicking the source table you want to duplicate > Script Table As > Create To > New Query Editor Window.

A script will get generated. Change the name of the table in generated script to whatever you want your new table to be called and click Execute. This way all the indexes, keys, constraints etc will be copied to new table.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post