asked
How to insert data from two tables into one new table
I've searched the forum and wasn't able to find a solution, so here it goes ... Currently, I have the following tables for my scenario: TABLE: Project COLUMNS: ProjectID, Name TABLE: Task COLUMNS: TaskID, ProjectID, Name TABLE: VolunteerGroup COLUMNS: VolunteerGroupID, Name TABLE: ProjectVolunteerGroups COLUMNS: ProjectID, VolunteerGroupID I would like to move tracking Volunteer info from the Project down to the Task. I have therefore created the following new table: TABLE: TaskVolunteerGroups COLUMNS: TaskID, VolunteerGroupID I have written the following to move data from the Project to the Task: insert into TaskVolunteerGroups (TaskID, VolunteerGroupID) select distinct(TaskID), 1 from Task where ProjectID in (select ProjectID from ProjectVolunteerGroups) I just hardcoded the "1" value for the VolunteerGroupID as I wasn't able to figure out how to grab the appropriate VolunteerGroup ID ... I tried using another select as the second parameter for the insert but it didn't work. I was thinking once I got the initial TaskIDs inserted, I could go back and do an update on the TaskVolunteerGroups based on a select from the initial ProjectVolunteerGroups table. I would love advice and assistance on the best way to do this. I am sure my way is not the best way to go about this. Thank you in advance! Jennifer
The benefit of "relational" is that you don't need to do that unless you're looking into a BI situation. You can create the new table and add the data at the same time by: SELECT (t.TaskID, g.VolunteerGroupID) INTO TaskVolunteerGroups FROM Task t JOIN ProjectVolunteerGroups g ON t.ProjectID = g.ProjectID INSERT INTO TaskVolunteerGroups (TaskID, VolunteerGroupID) SELECT t.TaskID, g.VolunteerGroupID FROM Task t JOIN ProjectVolunteerGroups g ON t.ProjectID = g.ProjectID
Thank you, dvroman. However, when I run it, I receive the following: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.
ahh, sorry about that! Thank you for your quick reply. Very helpful!!
15 People are following this question.
