在表中生成固定数量的行

问题描述

无法正确表达问题，因此无法搜索我想要的内容.我所需要的只是一个带有一列say guids的虚拟表，我将它用于其他目的.没有实际编写相同的 insert .. newID() n 次，想知道是否有一个优雅的解决方案.

Not able to word the question properly, so couldn't search what I want. All I need is a dummy table with a single column of say guids, which I use it for some other purposes. Without actually writing same insert .. newID() n times, wondering if there is an elegant solution.

类似的问题是如何用一个 int 列填充一个空白表，比如 1-n 个数字.

Similar question would be how do I populate a blank table with a int column with say 1-n numbers.

Row1: 1
Row2: 2
.......
Row100:100

推荐答案

这种方法非常快.如果您需要从无到有生成数字表，这可能是可用的最佳"方法.

This method is blisteringly fast. If you need to generate a numbers table from nothing, it's probably the "best" means available.

WITH
  t0(i) AS (SELECT 0 UNION ALL SELECT 0),
  t1(i) AS (SELECT 0 FROM t0 a, t0 b),
  t2(i) AS (SELECT 0 FROM t1 a, t1 b),
  t3(i) AS (SELECT 0 FROM t2 a, t2 b),
  n(i) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM t3)
SELECT i FROM n WHERE i BETWEEN 1 AND 100