Tuesday, November 11, 2008

Care about “Create Table AS Select” statement

Hi folks,

I had one misassumption on the SQL statement CREATE TABLE AS SELECT in database operations. We are using this form of statement for creating a table from an existing table.

Ex.  Create table New_tab as select * from Old_tab;

Yes, it is working, table creation is done. But, what I thought about it is, it will create that new_tab table with constraints & index which are available in old_tab. But, only table is created with the values (if any exists) ; constrains, primary key & index are not created for the new_tab table, even they are present in old_tab. I tried this in MySQL.

It seems this not only for MySQL, I will check any option needs to provide, to get result as expected.

If anyone have any suggestions / content about this, update me.