CONNECT BY PRIOR - Hierarchical Queries - Oracle to SQL Server Migration

In Oracle, you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. Microsoft SQL Server (MSSQL) allows you to use Recursive Commom Table Expressions (CTE) to get the same functionality.

One of the simplest use of CONNECT BY is to generate an arbitrary number of rows. For example, the following query generates 5 rows:

Oracle:

  SELECT level
  FROM dual
  CONNECT BY level <= 5;

Result:

In SQL Server you can use the following recursive common table expression to get the same result:

SQL Server:

  WITH cte_connect_by AS (
     SELECT 1 AS level
     UNION ALL
     SELECT level + 1 AS level FROM cte_connect_by WHERE level <= 4
  )
  SELECT level
  FROM cte_connect_by;

Result:

For more information, see Oracle to SQL Server Migration.