CTE - SQL Server Vs ORACLE

Common table expression(CTE) can be used both in SQL Server and Oracle. However there is a significant difference between them if you use cte to insert data to a table. In SQL server, you need to create a cte first and then insert data to a table. But in ORACLE cte definition should be preceded by insert statement. 

SQL Server

create table test(i int) GO ;with cte (i) as

(  select 1 union all  select 2 ) 

insert into test(i) select i from cte select i from test  

ORACLE

create table test(i number) 

 insert into test with cte  as (  select 1 from dual union all  

 select 2 from dual )

select * from cte select * from testing1 

compile by Divyang Panchasara Sr. Programmer Analyst Hitech OutSourcing