Monday, February 9, 2015

Generate rows and sequence data in Oracle and SQL Server


There is a simple and efficient way in Oracle to generate arbitrary number of rows and a sequence of integer numbers. The trick is to (ab)use Oracle's "connect by" clause. From the integer sequence, it is trivial to derive other sequential data, such as datetime or varchar. Here is an example,

SQL Server

SQL Server doesn't support "connect by" clause. However, you can use recursive CTE (common table expression) to get similar results, albeit more verbosely. Here is the same example in T-SQL,

No comments: