Latest Posts

Tuesday, December 25, 2007

how to create colon of a existing table

we often require colon of table to take backup .Creating colon of table in different database requires different queries.these colons are used for temporary data manipulation. Some of the different queries used in different database are explain below.


MySql
CREATE TABLE new_table_name like existing_table_name
this will create new table with all the property of old table (like primary key , auto increment)

CREATE TABLE new_table_name select * from old_table
this query will create new table with all data and and structure but property like primary key are drooped
CREATE TABLE new_table_name (ID INT auto_increment primary key ) select * from old_table
this query will create new table with old table structure and with a new column " ID " .
if ID exist in old table then it will assign the property of primary key and auto increment to ID column;


ORACLE
CREATE TABLE newTable AS SELECT * FROM oldTable WHERE 1= 2;
This will create table newTable with the same columns as oldTable.
It will have no constraints or indexes. It will have zero(o) rows in it.
CREATE TABLE newTable AS SELECT * FROM oldTable WHERE 1= 1;
OR
CREATE TABLE newTable AS SELECT * FROM oldTable;
This will create table newTable with the same columns as oldTable and with all data of old table.

SQL 2000
SELECT * INTO emp_new from emp where 1=2
This will create the table structure alone.
SELECT * INTO emp_new from emp where 1=1
This will create the table structure with data.

No comments:

Post a Comment