how to create new table which structure should be same as another table
i tried, Create table dom as select * from dom1 where 1=2 but its not working error occured
+5
A:
Try:
Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2
Note that this will not copy indexes, keys, etc.
If you want to copy the entire structure, you need to generate a Create Script of the table. You can use that script to create a new table with the same structure. You can then also dump the data into the new table if you need to.
If you are using Enterprise Manager, just right-click the table and select copy to generate a Create Script.
Kevin Crowell
2010-03-24 06:23:11
Kevin, just a small formatting change in your answer:-Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2
ydobonmai
2010-03-24 06:32:19
@Ashish Fixed, thanks.
Kevin Crowell
2010-03-24 06:35:30
why to add where clause here 1=2 ???
BreakHead
2010-03-24 06:44:03
Qutbuddin, 1=2 will prevent data copying from source to destination table. Try yourself:- CREATE TABLE Table1 ( Id int , Name varchar(200) ) INSERT INTO table1 VALUES (1,'A') INSERT INTO table1 VALUES(2,'B') -- Will create table2 with data in table1 SELECT * INTO Table2 FROM Table1 WHERE 1=2 -- Will create table2 without data in table1 SELECT * INTO Table2 FROM Table1 WHERE 1=2
ydobonmai
2010-03-24 06:57:42
The SQL scripts in the comment do not appear correctly. :-)
ydobonmai
2010-03-24 06:58:33
+1
A:
I don't know why you want to do that, but try:
SELECT *
INTO NewTable
FROM OldTable
WHERE 1 = 2
It should work.
Adrian Faciu
2010-03-24 06:28:39