SQL Server 2008：复杂插入

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

Answer 2

如果您有一个数字表，您可以加入它以获取各个年份的记录并避免使用游标。我只是用 1965 到 968 之间的数字填充数字表，但是现实生活中的数字表（出于示例目的，它也不会是如下所示的临时表，而是存在于您的模式中的表）可能有几百万条记录，因为它对于很多比较很有用。

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

Answer 3

是的，你实际上必须做一个循环...我不想使用游标，但这种情况有点有意义...无论如何，这里的代码只是一个直接循环，向您展示您可以执行这种代码在 SQL 中：

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

Answer 4

您可以使用递归 CTE：

;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)

Answer 5

您可以使用递归过程。就像下面这个：

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
}

Answer 6

你可以这样做，但如果 Begin 或 end 超过 2047，它将失败

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