CODEWITHSUNDEEP
sql-server-2008
user1840281
user1840281

Reputation: 21

SQL Server 2008 How to create multiple tables for each record in a table?

I have a table that contains consultant's first name, last name, skill name, and certification which basically shows if the consultant is certified in their skill. I am trying to create a script that will generate a table for each consultant with two columns (skills, and certification status) some consultants may have more than one skill. I am thinking about the while loop syntax or dynamic sql but have no luck. I have searched the web thoroughly with no luck.

Upvotes: 0

Views: 1616

Answers (3)

user1840281
user1840281

Reputation: 21

The answer that worked for me based on the lab question's parameters was:

USE Software

Declare @TableNameVar varchar(1000)
Declare @ConsultantIDVar int, @ConsultantIDMax int

Set @ConsultantIDVar = (Select MIN(ConsultantID) From Consultant)
Set @ConsultantIDMax = (Select MAX(ConsultantID) From Consultant)

While @ConsultantIDVar <= @ConsultantIDMax
Begin

   Set @TableNameVar = (Select (ConsultantFName + '_' + ConsultantLName)
                                From Consultant
                                Where ConsultantID = @ConsultantIDVar)

   Set @TableNameVar = 'Create Table '+ @TableNameVar + ' (SkillDescription varchar(40), Certification bit)'
   Exec (@TableNameVar)

   Set @ConsultantIDVar = @ConsultantIDVar + 1

End

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Instead of your proposed solution, I think you want a junction, or mapping table.

Consultant
    - ID
    - First_Name
    - Etc...

Skill
    - ID
    - Name

Consultant_Skill
    - Consultant_ID
    - Skill_ID

The consultant_skill table will allow you to have a many-to-many relationship between consultant and skill.

Upvotes: 1

ItsPete
ItsPete

Reputation: 2368

You're probably best going about this a different way. Instead of creating tables for each record in another table, just create one second table that links to the first in a one-to-many relationship.

For example:

Consultant
----------
ConsultantID
Name


Skill
-----
SkillID
SkillName
F_ConsultantID

Upvotes: 2

Related Questions