SQL SERVER: Copy structure of an existing table without data into new table
Create a table named "tblStudents" in database and insert some dummy data into it.
CREATE TABLE tblStudents
StudentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Query to insert data into "tblStudents"
Insert Into tblStudents values
Table "tblStudents" data can be seen by firing following query :-
Select * from tblStudents
Output of above query :-
There are two ways of copying structure of an existing table without data into new table
- The following is the first query to be executed
SELECT * INTO tblNewStudents FROM tblStudents WHERE 1=0
2. The following is the second query which can be executed
SELECT TOP 0 * INTO tblNewStudents FROM tblStudents
Both the above queries will create a new table "tblNewStudents" having same structure as table "tblStudents". The columns of table "tblStudents" along with datatype are copied but these quries will not copy data, indexes, keys, constraints etc of the source table to target table.
The following query is fired to see table "tblNewStudents"
select * from tblNewStudents
Output is an empty table :