How to copy table structure

vbRad Home

Source Code

Book Reviews

Forum

Links

About Us

Contribute

Compare Databases with SQL Effects Clarity

How to copy table structure
Posted on 2/18/2001	Author: Robert Gelb	Email: Not Shown
Applies To OS: All		Product: N/A

Say you want to copy the structure of the table without copying data. Then, use this simple SQL statement.

SELECT *
INTO my_new_table
FROM my_old_table
WHERE 1=2

This code copies the structure of table my_old_table and creates a new table called my_new_table with identical structure.

Note, that the INTO clause is what is known as a non-logged operation, so your database (Oracle, MS SQL Server, Sybase), although unlikely, may not allow it (depends on the administrator). However, this method always works in Access or on .MDB files.

Add Your Comment

Name:			Email Address:		all fields optional

Notify me via email when someone responds to this message (valid email required).

Enter the word:

Comments

#1. By Geetha. Posted on 2/6/2007 12:36:11 PM

Its was very useful.

#2. By lalitha. Posted on 2/15/2007 5:10:37 AM

It is giving an error as missing keyword at new_table_name.
do i need to cretae a new table before copying

#3. By lalitha. Posted on 2/15/2007 5:16:01 AM

It is giving an error as missing keyword at new_table_name.
do i need to cretae a new table before copying

#4. By Dipan Patel. Posted on 2/19/2007 9:53:48 AM

This is not working..becoz no reference or primary key is generated if parent table has.

#5. By MJ. Posted on 4/13/2007 1:23:11 PM

This works but only to a certain extent.

Constraints and indexs on original table are not created in new table :(

#6. By Vinod. Posted on 5/10/2007 8:46:55 AM

it works .. i wanted to create dummy (staging) table .. this worked fine!

#7. By siva. Posted on 5/26/2007 2:39:00 AM

thanks

#8. By Indriatno. Posted on 7/5/2007 10:57:54 AM

Thanks...!!

#9. By Ed Hitchcock. Posted on 8/2/2007 3:54:56 AM

I wanted to copy a table to get empty table with autonumber set to 1.
That method worked perfectly.

#10. By Rajiv Gandhi. Posted on 8/16/2007 11:26:29 AM

Its Very nice one

#11. By Ganesan.R. Posted on 9/21/2007 7:08:52 AM

Super...

#12. By Sunil Naik. Posted on 11/12/2007 11:49:31 AM

SELECT *
INTO my_new_table
FROM my_old_table
WHERE 1=2

The above command justs creates the strucute. It doesnot create indexs and constraints
please help

#13. By Anonymous. Posted on 11/12/2007 5:38:22 PM

To #12. Indexes and constraints you have to do manually.

#14. By Anonymous. Posted on 1/17/2008 8:03:56 AM

The above string will copy the structure of the original table, but it WILL NOT copy attributes as pimary key, indexes, relationships.

#15. By Yura. Posted on 2/19/2008 3:04:18 PM

This looks like an SQL Server statement.

In oracle it would look like this:

create table my_new_table as
select * from my_old_table where 1=2;

#16. By sandeep. Posted on 2/20/2008 10:20:08 AM

can any one please tell why " WHERE 1=2 "

#17. By Anonymous. Posted on 2/22/2008 8:56:35 PM

To #16. WHERE 1=2 ensures that no rows will be returned, only structure.

#18. By Naod Adnew. Posted on 4/3/2008 9:53:15 AM

I AM A NEW USER FOR YOUR APPLICATION THANKS FOR HELP ME

#19. By Dori Lal. Posted on 4/3/2008 4:54:40 PM

Very Very good friend and instant teacher

#20. By RAMASHANKAR. Posted on 4/11/2008 11:13:24 AM

thank's to giving such explation where 1=2

#21. By Dei. Posted on 5/19/2008 8:58:50 AM

Thanks,simple and it pass.

#22. By Rathessh R. Posted on 7/15/2008 4:00:03 AM

I want to copy the content of table on Access to Word or Exel to avoiding the extra time to type those data. pls help.

#23. By devender. Posted on 7/25/2008 5:56:01 AM

the above query is useful but this will only copy the structure but not the existing indexes on the columns of the original table. do we have any query which will copy the whole structure with the indexes

#24. By Anonymous. Posted on 7/31/2008 8:32:39 PM

thanks a lot !

#25. By sree. Posted on 8/21/2008 12:45:14 PM

thanks u very much for giving good info.. to me..such as where 1=2

#26. By Anonymous. Posted on 11/29/2008 7:16:34 AM

this is not working..

#27. By Anonymous. Posted on 1/15/2009 5:58:23 PM

Very useful. Works fine to copy proxy tables with sybase.

#28. By juan andres. Posted on 2/24/2009 8:16:41 PM

ahhaha ahhahaha ahahahaha so fuck&%&$ing cool!
Thanks !

#29. By Anonymous. Posted on 3/10/2009 11:18:41 AM

Thank you....It was very usefull

#30. By deepika. Posted on 3/18/2009 12:15:32 PM

this doesnt work!

#31. By roebean. Posted on 4/28/2009 3:40:10 AM

Its was very useful, but how about copy structure from the other database??
not in 1 database?
regards

#32. By roebean. Posted on 4/28/2009 4:03:31 AM

Its was very useful, but how about copy structure from the other database??
not in 1 database?
regards

#33. By roebean. Posted on 4/28/2009 4:18:25 AM

Its was very useful, but how about copy structure from the other database??
not in 1 database?
regards

#34. By Ashish. Posted on 5/25/2009 5:02:05 AM

i want a code for copy access table from access to sql server in asp.net on click of button

#35. By Nithya Mohanrajh. Posted on 7/7/2009 5:26:38 AM

Thanks.This works for me...

#36. By kk. Posted on 7/31/2009 12:49:18 PM

how i copying only the table structure to new table

#37. By kk. Posted on 7/31/2009 12:52:49 PM

CREATE TABLE learning AS SELECT * FROM learn_process i fallow this query but the error was"Incorrect syntax near the keyword 'AS'"

#38. By Bhargavi Goswami. Posted on 8/3/2009 6:10:22 AM

Hi. Thanks. It really works.

select * into database..table2 from database..table1 where 1=2

#39. By Anonymous. Posted on 8/25/2009 12:13:26 PM

Thanks yar it's really working fine.

#40. By Ashok. Posted on 9/11/2009 5:44:59 PM

Thanks its working...but Where 1=2...i'm not getting the logic of this.......generally we compare variables in Where clause...

#41. By vijay. Posted on 9/28/2009 12:00:45 PM

This query is not ok. If u want to create trhe table than use
CREATE TABLE newTable AS
SELECT *
FROM oldTable
WHERE 1= 2;

congrats......

#42. By Vijay. Posted on 9/28/2009 12:03:00 PM

dear Ashok,
1=2 only refers to that we dont want any data out of the table.This where clause returns nothing so only structure gets copied.....

congrats..............

#43. By Maintex. Posted on 10/28/2009 1:09:46 PM

Alternatively, a perhaps more correct way of doing this would be:

SELECT TOP 0 *
INTO my_new_table
FROM my_old_table;

This works and has the added advantage of not hurting the brain cells of those that could not grasp the logic of the where clause of your SQL statement ;)

#44. By Jessu P. Posted on 12/2/2009 10:38:25 AM

Thank u

#45. By Badass. Posted on 2/25/2010 5:04:44 PM

didnt work

#46. By one. Posted on 4/15/2010 6:54:58 AM

thanxxxx alot

#47. By jojoba. Posted on 6/17/2010 8:04:35 PM

thank you

#48. By kumaran. Posted on 7/13/2010 6:54:01 AM

i want to copy the structure of the table for the select field