CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Marcel Palm
Marcel Palm

Reputation: 47

Insert into sql database one to many relationship

Guys I would really appreciate it if someone could help me with this. I have a problem inserting records into a one to many relationship database. The scenario is as follow, I have a technician table(techid,name,surname) and a skill table(skillid, skillDesc, techid). The skill table has a techID foreign key.

How can i create a sql statement/procedure to insert a technician with multiple skills assigned to the technician?

Upvotes: 3

Views: 8718

Answers (2)

Brenden Brown
Brenden Brown

Reputation: 3215

I think you should have three tables: Technician(techid, name, surname), Skill(skillid, skillDesc), and Ability(skillid, techid). If you only have two tables, each mapping of a skill to a technician would have to include a copy of the skillDesc, which is redundant information. Instead, you want to tables to define the Technician objects and the Skill objects, and a third table to capture the relationships between them.

First execute this query:

insert into technician values (my_id, my_name, my_surname)

then loop over the skills (psuedocode):

for skill in skills:
    insert into Ability values(skill, my_id)

Upvotes: 2

Waqar Janjua
Waqar Janjua

Reputation: 6123

You need three tables Technician, Skill and TechnicianSkill. Using two tables you can not assign multiple skills to a single technician. ( IF TechID and SkillIDs are primary key )

Technician Table:
Columns: TechID, Name, SurName

Skill Table:
Columns: SkillID, SkillDesc

-- Put the TechID and SkillID as a foreign key in this table
TechnicianSkill Table:
Columns: TechID, SkillID 
-- You can make a primary key by using the two columns (TechID,SkillID)

Upvotes: 1

Related Questions