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. 


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.
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..