sang3r sang3r - 7 months ago 41
SQL Question

Insert into associative table

I'm trying to insert into an Associative table on Visual Studio 2013, but I also believe I could have the structure for the tables set up incorrectly. What I need to happen is the Associative table needs to take the primary keys of both the Tasks and Student tables.

Expected Result:

StudentID TaskID Status
----------- ----------- ------
1 1 N
1 2 N
1 3 N
1 4 N
1 5 N
1 6 N
1 7 N
2 1 N
2 2 N
2 3 N
2 4 N
2 5 N
2 6 N
2 7 N


Any help would be greatly appreciated!




CREATE TABLE [dbo].[STUDENT] (
[StudentID] INT IDENTITY (1, 1) NOT NULL,
...
PRIMARY KEY CLUSTERED ([StudentID] ASC));


CREATE TABLE [dbo].[TASKS] (
[TaskID] INT NOT NULL,
[TaskName] NVARCHAR (MAX) NULL,
[TaskCategory] NVARCHAR (MAX) NULL,
PRIMARY KEY CLUSTERED ([TaskID] ASC));


CREATE TABLE [dbo].[ASSOCIATIVE] (
[StudentID] INT NOT NULL,
[TaskID] INT NOT NULL,
[Status] NCHAR (10) NULL,
PRIMARY KEY CLUSTERED ([TaskID] ASC, [StudentID] ASC),
CONSTRAINT [FK_ASSOCIATIVE_STUDENT] FOREIGN KEY ([StudentID]) REFERENCES [dbo].[STUDENT] ([StudentID]),
CONSTRAINT [FK_ASSOCIATIVE_TASKS] FOREIGN KEY ([TaskID]) REFERENCES [dbo].[TASKS] ([TaskID]));

Answer

What you want is a CROSS JOIN:

INSERT INTO ASSOCIATIVE(StudentID, TaskID, Status)
SELECT
    s.StudentID, t.TaskID, 'N'
FROM STUDENT s
CROSS JOIN TASKS t

If you want to run this again and you want to avoid PK violation, add a NOT EXISTS condition:

WHERE NOT EXISTS(
    SELECT 1 
    FROM ASSOCIATIVE a
    WHERE
        a.TaskID = t.TaskID
        AND a.StudentID = s.StudentID
)