CODEWITHSUNDEEP
sqlt-sql
Alan Fisher
Alan Fisher

Reputation: 2055

How to perform a mass SQL insert to one table with rows from two seperate tables

I need some T-SQL help. We have an application which tracks Training Requirements assigned to each employee (such as CPR, First Aid, etc.). There are certain minimum Training Requirements which all employees must be assigned and my HR department wants me to give them the ability to assign those minimum Training Requirements to all personnel with the click of a button. So I have created a table called TrainingRequirementsForAllEmployees which has the TrainingRequirementID's of those identified minimum TrainingRequirements.

I want to insert rows into table Employee_X_TrainingRequirements for every employee in the Employees table joined with every row from TrainingRequirementsForAllEmployees. I will add abbreviated table schema for clarity.

First table is Employees:

EmployeeNumber  PK  char(6)
EmployeeName        varchar(50)

Second Table is TrainingRequirementsForAllEmployees: 

TrainingRequirementID     PK  int

Third table (the one I need to Insert Into) is Employee_X_TrainingRequirements:

TrainingRequirementID  PK  int
EmployeeNumber         PK  char(6)

I don't know what the Stored Procedure should look like to achieve the results I need. Thanks for any help.

Upvotes: 2

Views: 65

Answers (1)

i-one
i-one

Reputation: 5120

cross join operator is suitable when cartesian product of two sets of data is needed. So in the body of your stored procedure you should have something like:

insert into Employee_X_TrainingRequirements (TrainingRequirementID, EmployeeNumber)
select r.TrainingRequirementID, e.EmployeeNumber
from Employees e
    cross join TrainingRequirementsForAllEmployees r
where not exists (
    select 1 from Employee_X_TrainingRequirements
    where TrainingRequirementID = r.TrainingRequirementID
        and EmployeeNumber = e.EmployeeNumber
    )

Upvotes: 2

Related Questions