SQL Server 2008：复杂插入

;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

create table #Numbers (Number int)
insert into #Numbers
select 1965
union
select 1966
union 
select 1967
union 
select 1968

create table #employees (name varchar (50), beginyear int, endyear int)
insert into #employees
select \'Dick\', 1966, 1968
union all 
select \'harry\', 1965, 1967
union all 
select \'tom\', 1955, 1966

insert into EmployeeYears (Name, [Year])
select Name, n.number 
from #Employees e
join #Numbers n on  n.number between e.beginyear and e.endyear
order by name

DECLARE @Employee VARCHAR(100)
DECLARE @BeginYear INT, @EndYear INT, @i INT

SET @Employee = \'\'

WHILE (1=1)
BEGIN
    SET @Employee = (SELECT TOP 1 Name FROM Employees ORDER BY Name WHERE Name > @Employee)

    IF @Employee IS NULL BREAK

    SELECT @BeginYear = BeginYear, @EndYear = EndYear FROM Employees WHERE Name = @Employee

    SET @i = @BeginYear

    WHILE (@i <= @EndYear)
    BEGIN
        INSERT INTO EmployeeYears (Year, Name) VALUES (@i, @Employee)
        SET @i = @i + 1
    END
END

;WITH CTE AS
(
    SELECT BeginYear, EndYear, Name
    FROM Employees
    UNION ALL
    SELECT BeginYear+1, EndYear, Name
    FROM CTE 
    WHERE BeginYear < EndYear
)  
INSERT INTO EmployeeYears (Year, Name)
SELECT BeginYear, Name
FROM CTE 
ORDER BY Name, BeginYear
OPTION(MAXRECURSION 0)

CREATE Procedure InsertYear
    @Name ....
    @BeginYear ...
    @EndYear ...
AS
{
     INSERT INTO  EmployeeYears  VALUES(@BeginYear, @Name);
     SET  @BeginYear = @BeginYear + 1
     IF @BeginYear < @EndYear
     BEGIN
         InsertYear(@Name, @BeginYear, @EndYear)
     END

     RETURN
}

INSERT INTO EmployeeYears (number, name)
SELECT v.number, e.name
FROM 
    Employees e
    INNER JOIN master..spt_values v on 
    v.number between beginYear and endYear