使用枚举数据初始化表

Question

我想初始化一个带有枚举数据的表，比如

| x | y | z | domain | 
|---|---|---|--------|
| 1 | 1 | 1 | a.com  | 
| 1 | 2 | 1 | a.com  | 
| 1 | 3 | 1 | a.com  |

通过运行如下存储过程：

exec init_table 10, 10, 1

一些表看起来像这样

| i | domain | 
|---|--------|
| 1 | a.com  |
| 2 | b.com  |

我有以下几点...

-- Set z to default if `null` is passed in
if (@num_coordinate_z is null) begin set @num_coordinate_z = 1 end

-- Initialize counters for loop
declare @x int = 1, @y int = 1, @z int = 1, @d int = 1
declare @num_domains int = (select distinct count(domain) from 
someDb..someTable)

while (@x <= @num_coordinate_x) begin
  while (@y <= @num_coordinate_y) begin
    while (@z <= @num_coordinate_z) begin
      while (@d <= @num_domains)      begin

        insert into configMotorsTest (
          coordinate_x,
          coordinate_y,
          coordinate_z,
          domain
        ) values (
          @x,
          @y,
          @z,
          (select [domain] from someDb..someTable where [i] = @d)
        )

        set @d = @d + 1

      end 
      set @x = @x + 1
     end
     set @y = @y + 1
   end
   set @z = @z + 1
 end

但它只写了一条记录。

如何使用存储过程exec初始化(10，10，1，2) (x，y，z，domain)表

Answer 1

这更适合于统计数据。到目前为止，WHILE循环是最糟糕的方法。它太慢了。

假设您有一个域地址表，那么您可以简单地执行以下操作：

WITH Tally AS(
    SELECT N
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))N(N))
SELECT N1.N AS x,
       N2.N AS y,
       N3.N AS z,
       D.Domain
FROM dbo.DomainTable D
     CROSS JOIN N N1
     CROSS JOIN N N2
     CROSS JOIN N N3;

这将为每个电子邮件地址创建1000 (10*10*10)行，包括x、y和z。

对于更“动态”的方法：

DECLARE @x int = 10,
        @y int = 10,
        @z int = 1;

DECLARE @m int = (SELECT MAX(V.N) FROM (VALUES(@x),(@y),(@z))V(N));

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@m)
           ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4) --this'll create 10,000 rows
SELECT T1.I AS x,
       T2.I AS y,
       T3.I AS z,
       D.Domain
FROM dbo.DomainTable D
     CROSS JOIN Tally T1
     CROSS JOIN Tally T2
     CROSS JOIN Tally T3
WHERE T1.I <= @x
  AND T2.I <= @y
  AND T3.I <= @z;

如果@x、@y和@z的值为1000，则这将创建1000^3 (1,000,000,000)行，并且它接受的值最高可达10,000。

但是，我不建议您真正尝试一次创建超过100万行；这只会填满您的事务日志，并可能导致失败。