SQL Server 2008: complex Insert

I have a table called Employees:

BeginYear  |   EndYear   |    Name
1974           1983           Robert

For 开发者_如何学Pythoneach record in Employees I need to insert each year into a new table called EmployeeYears

So:

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

Any Way to do this in SQL...possibly with cursors?

The gist of it is using a WITH statement to create all the records and use them to insert into your final table.

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

Testdata

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'

Results

SELECT  *
FROM    EmployeeYears
ORDER BY Name, Year

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

If you have a numbers table you can join on it to get the individual year records and avoid using a cursor. I just poulated the numbers table with number from 1965 to 968, but a realife numbers table (which also would not be a temp table as shown below for example purposes, but one that lives in your schema) would probably have several million records as it is useful for a lot of comparing.

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

Yes, you actually have to do a loop... I'd prefer not using CURSORS, but this case sorta makes sense... anyway, here's the code as just a straight loop to show you that you can do that kind of code in 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

You can use a recursive 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)

You can use a recursive procedure. Llike the one bellow:

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
}

You could do this but it will fail if Begin or end exceeds 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