Showing posts with label select into. Show all posts
Showing posts with label select into. Show all posts

Friday, May 09, 2014

Oracle Create Table As Select vs SQL Server Select Into

Oracle CTAS (create table as select) is a convenient way to create a new table based on an existing table.

SQL> select * from tbl_test;

       NUM
----------
         1
         2

SQL> create table tbl_test2 as select * from tbl_test;

Table created.

SQL> select * from tbl_test2;

       NUM
----------
         1
         2
In SQL Server, instead of using CTAS, we use "select.. into new_table from old_table" to create a new table based on old table.
select * into tbl_test2 from tbl_test;

Sunday, August 26, 2012

Create new tables in Oracle and SQL Server


In Oracle, we can use  "create table as select" as shown below.
1. Copy table into a new table.
create table tbl_x as select * from tbl_test;
2.Create a  new empty table with the same structure.
create table tbl_y as select * from tbl_test where 2=1;

In SQL Server,  we can use select into as shown below.
1. Copy table into a new table.
select * into  tbl_x   from  tbl_test ;
2. Create a  new empty table with the same structure.
select * into  tbl_y   from tbl_test  where 2=1;