title: Insert data in table depending on 2 other tables
body: <p>I have a problem with filling a 3rd table depending on the data of 2 other tables. I have no idea how to solve my problem and I hope someone could show me in the right way or maybe the right way itself. 
  I'm not very experienced with SQL so I probably don't know something which makes me search in the wrong direction</p>
  
  <pre><code>CREATE TABLE UserProfiles
  (
     UserProfileId bigint NOT NULL identity(1,1) constraint pk_UserProfileId primary key,
     DescriptionTranslationId bigint not null,
     [Description] varchar(max),
     AuditModifiedOn datetime2,
     AuditModifiedBy varchar(MAX),
     AuditDeleted bit not null default '0',
     AuditCreatedOn datetime2 NOT NULL default GETDATE(),
     AuditCreatedBy varchar(MAX) NOT NULL
  )
  
  CREATE TABLE ProjectTypes
  (
     ProjectTypeId bigint NOT NULL identity(1,1) constraint pk_ProjectTypeId primary key,
     ProjectTypeName varchar(max),
     DescriptionTranslationId bigint NOT NULL,
     AuditModifiedOn datetime2,
     AuditModifiedBy varchar(MAX),
     AuditDeleted bit not null default '0',
     AuditCreatedOn datetime2 NOT NULL default GETDATE(),
     AuditCreatedBy varchar(MAX) NOT NULL,
  )
  
  CREATE TABLE UserProfileProjectType
  (
     UserProfileProjectTypeId bigint NOT NULL identity(1,1) constraint pk_UserProfileProjectTypeId primary key,
     UserProfileId bigint not null,
     ProjectTypeId bigint not null,
     AuditModifiedOn datetime2,
     AuditModifiedBy varchar(MAX),
     AuditDeleted bit not null default '0',
     AuditCreatedOn datetime2 NOT NULL default GETDATE(),
     AuditCreatedBy varchar(MAX) NOT NULL,
     constraint fk_UserProfileProjectType_UserProfileId_UserProfiles foreign key (UserProfileId) references UserProfiles(UserProfileId),
     constraint fk_UserProfileProjectType_ProjectTypeId_ProjectTypes foreign key (ProjectTypeId) references ProjectTypes(ProjectTypeId)
  )
  </code></pre>
  
  <p>The first 2 tables are filled with data and the 3rd needs to be filled with the ID from the userProfiles and projectTypes table. I insert the data in the userProfiles and the ProjectTypes tables like this</p>
  
  <pre><code>insert into dbo.UserProfiles (DescriptionTranslationId, [Description], AuditCreatedBy)
  values 
  (9999, 'Super User', 'email'),
  (9999,  'Support Admin', 'email'),
  (9999, 'Support Manager','email'),
  (9999, 'Dev Admin', 'email'),
  (9999, 'Dev Manager', 'email'),
  (9999, 'Dynapps Admin', 'email'),
  (9999, 'Dynapps Manager', 'email');
  
  insert into ProjectTypes (ProjectTypeName, DescriptionTranslationId, AuditCreatedBy)
  values
  ('Managed services', 9999, 'email'),
  ('Support', 9999, 'email'),
  ('Dev', 9999, 'email'),
  ('Dyn', 9999, 'email');
  </code></pre>
  
  <p>Now I want to insert information into the 3rd table (userProfileProjectTypes) depending on the data from the 2 other tables.</p>
  
  <p>So when the userProfile is Super User the projectType it needs are Managed services, support, dev and Dyn. So the userProfileProjectType table should have 4 rows with the same userProfileId and 4 times a different projectTypeId. Eventually I have to do this for every userProfile (where for userProfileId 'dev manager' only the projectTypeId 'dev' should sit in the userProfileProjectType table). I have no idea how to achieve that. I did try some stuff like this. But it did not work. </p>
  
  <pre><code>Select UserProfileId, [Description],
  CASE 
     WHEN [Description] = 'Super User' THEN (insert into UserProfileProjectType (UserProfileId, ProjectTypeId, AuditCreatedBy) values (UserProfileId), (select ProjectTypeId from ProjectTypes where ProjectTypeName = "Managed services"), ('email'))
     WHEN [Description] = 'Super User' THEN (insert into UserProfileProjectType (UserProfileId, ProjectTypeId, AuditCreatedBy) values (UserProfileId), (select ProjectTypeId from ProjectTypes where ProjectTypeName = "Support"), ('email'))
     WHEN [Description] = 'Super User' THEN (insert into UserProfileProjectType (UserProfileId, ProjectTypeId, AuditCreatedBy) values (UserProfileId), (select ProjectTypeId from ProjectTypes where ProjectTypeName = "Dev"), ('email'))
     WHEN [Description] = 'Super User' THEN (insert into UserProfileProjectType (UserProfileId, ProjectTypeId, AuditCreatedBy) values (UserProfileId), (select ProjectTypeId from ProjectTypes where ProjectTypeName = "Dynapps"), ('email'))
     ELSE 
  END
  from UserProfiles
  </code></pre>