Insert rows into same table using data from different rows based on data from another table

I’ve got 2 tables: TimecardSetDetails, TimecardDetails

TimecardSetDetails

+--------------+-------------+--------------+
| SetDetailsID | EmployeeKey | SetHistoryID |
+--------------+-------------+--------------+
|       146358 |        6023 |        10471 |
|       146357 |        2933 |        10471 |
|       146359 |       27334 |        10471 |
+--------------+-------------+--------------+

TimecardDetails

+--------------+-------------+------------+---------+
| SetDetailsID | EmployeeKey | ProjectKey | TaskKey |
+--------------+-------------+------------+---------+
|       146358 |        6023 | NULL       | NULL    |
|       146358 |        6023 | 22172      | 823930  |
|       146358 |        6023 | 22172      | 840709  |
|       146358 |        6023 | 22306      | 815854  |
|       146357 |        2933 | NULL       | NULL    |
|       146359 |       27334 | NULL       | NULL    |
+--------------+-------------+------------+---------+

I need help on inserting each ProjectKey and TaskKey for EmployeeKey = 6023 in the TimecardDetails table (3 rows in this example) into the same TimecardDetails table for each row in the TimecardSetDetails table where SetHistoryID = 10471 but with the new EmployeeKey.

I would like the results to be this:

TimecardDetails

+--------------+-------------+------------+---------+
| SetDetailsID | EmployeeKey | ProjectKey | TaskKey |
+--------------+-------------+------------+---------+
|       146358 |        6023 | NULL       | NULL    |
|       146358 |        6023 | 22172      | 823930  |
|       146358 |        6023 | 22172      | 840709  |
|       146358 |        6023 | 22306      | 815854  |
|       146357 |        2933 | NULL       | NULL    |
|       146357 |        2933 | 22172      | 823930  |
|       146357 |        2933 | 22172      | 840709  |
|       146357 |        2933 | 22306      | 815854  |
|       146359 |       27334 | NULL       | NULL    |
|       146359 |       27334 | 22172      | 823930  |
|       146359 |       27334 | 22172      | 840709  |
|       146359 |       27334 | 22306      | 815854  |
+--------------+-------------+------------+---------+

Answer

You can generate the rows with a joinand then insert them into the TimecardDetails table:

insert into TimecardDetails (SetDetailsID, EmployeeKey, ProjectKey, TaskKey)
select s.SetDetailsID, s.EmployeeKey, d.ProjectKey, d.TaskKey
from TimecardDetails d
inner join TimecardSetDetails s on 
    s.EmployeeKey <> d.EmployeeKey
where 
    d.EmployeeKey = 6023
    and d.ProjectKey is not null
    and s.SetHistoryID = 10471

Advertisement

Answer