Create Table 'As' In Oracle

[vnk]

Hi All,

Is there any command equivalent to "Create table tablename as select * from table2" in SqlServer as in Oracle.

Thanks In Advance,
VNK.

 

[tlbroadbent]

To create a table structure and insert data from the old table use the following.

Select * Into NewTable
From OldTable

If you only want the structure do the following.

Select * Into NewTable
From OldTable
Where 1=2 -- always false so no rows will be selected Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[vnk]

Thanks A Lot for the input, but I ended up having a different problem. It complains "Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option"

I did run the 'exec sp_dboption', still it doesnt work.
Is there a solution for this.

Thanks,
VNK.

 

[tlbroadbent]

You need to provide parameters for sp_dboption.

exec sp_dboption 'dbname', 'select into/bulkcopy', 'True' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[vnk]

Tlbroadbent,

Thanks Man! It works, really cool.

-VNK.

 

[tlbroadbent]

Did you set the option to False after completing the Insert? Keeping the option on and allowing Bulk Inserts can cause Transation Log backups to fail. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.