SQL 服务器 2008 : complex Insert

Question

我有一个名为 Employees 的表:

BeginYear  |   EndYear   |    Name
1974           1983           Robert

对于 Employees 中的每条记录，我需要将每年插入一个名为 EmployeeYears 的新表中

所以:

For Each Record in Employees
    For i as int = Begin Year to End year
        INSERT i, Name into EmployeeYears

有什么方法可以在 SQL 中执行此操作...可能使用游标？

Answer 1

它的要点是使用 WITH 语句创建所有记录并使用它们插入到您的最终表中。

;WITH q AS (
  SELECT Year = BeginYear
         , Name
  FROM   Employees
  UNION ALL
  SELECT q.Year + 1
         , q.Name
  FROM   q
         INNER JOIN Employees e ON e.Name = q.Name
                                   AND e.EndYear > q.Year
)
INSERT INTO EmployeeYears
SELECT * FROM q
OPTION(MAXRECURSION 0)

测试数据

CREATE TABLE Employees (BeginYear INTEGER, EndYear INTEGER, Name VARCHAR(32))
CREATE TABLE EmployeeYears (Year INTEGER, Name VARCHAR(32))

INSERT INTO Employees
  SELECT 1974, 1976, 'Robert'
  UNION ALL SELECT 1972, 1975, 'Lieven'

结果

SELECT  *
FROM    EmployeeYears
ORDER BY Name, Year

1972    Lieven
1973    Lieven
1974    Lieven
1975    Lieven
1974    Robert
1975    Robert
1976    Robert