Sunday, April 12, 2009

DBMS : Simple method to create duplicate tables in Oracle and MS SQL

Oracle



1. Creating duplicate table with data in Oracle

Here New Table is exact replica of old table with same columns, same datatype with same data

create table new_table as
select * from old_table

2. Creating duplicate table with no data in oracle, only structure is copied

This is simple method to create New blank Table with same structure as old table. Just provide a false condition in where clause which will never be true

create table new_table as
select * from old_table
where ( any_column = 'false condition' )



MS SQL



1. Creating duplicate table with data in MS SQL

Similar to oracle here also new table is exact replica of old table with all data.

select * into new_table
from old_table

2. Creating duplicate table structure in MS SQL

Here also only table structure is copied with no data, just provide a false condition in where clause which will never be true.

select * into new_table
from old_table
where ( any_column = 'false_condition' )