CODEWITHSUNDEEP
sqlsql-server-2005
Churchill
Churchill

Reputation: 81

how to create a duplicate table with no data in it

how to create a duplicate table with no data in it

old tablename--student

newtablename--student1

plz tell me the exat query

thanks, Churchill..

Upvotes: 2

Views: 24708

Answers (7)

Mark
Mark

Reputation: 1

In Sql Server you can right click on the table name in the Object Explorer.

Select "Script Table as" > "CREATE To" and then select "New Query Editor Window" This creates a query to create the table.
In this case table name [student] change this to [student1] in the query and run and you have an exact copy of the schema, indexes, vartypes, primary keys etc.

Upvotes: 0

Ashwini
Ashwini

Reputation: 851

create table student1 as select * from student;

In the above query student1 is the table which has the exact copy of the already existing table student which copies the entire data.

create table student1 as select * from student where 0=1;

The query copies only the columns present in the student table but no data will be copied

But please note, you can't copy the constraints and indexes

Upvotes: 3

yasas liyanapathirana
yasas liyanapathirana

Reputation: 21

First you can copy the whole table by:

select * into (your_table_name) from student

After copying the whole table, you delete the data by running:

truncate table (your_table_name);

Upvotes: 2

Sandeep C
Sandeep C

Reputation: 9

create table newtable as select * from oldtable where clause

Upvotes: 0

Rawheiser
Rawheiser

Reputation: 1218

select * into student1 from student where 1=2

This will get you the columns, but indexes and other objects will require scripting with a database tool of some sort.

Upvotes: 1

Mark Wilkins
Mark Wilkins

Reputation: 41222

select * into student1 from student where 1=0

Upvotes: 2

user114600
user114600

Reputation: 

SELECT TOP 0 * INTO student1 FROM student

Upvotes: 7

Related Questions