locked
I'm not using the link table in this 3 table Task Resources TaskResources design RRS feed

  • Question

  • Given this screenshot of my Query surface and the Tasks, Resources and TaskResources DDL below, I'm not utilizing the one to many (one task to many resources) possibilty in my design. How do I create insert queries so the TaskResources link table gets utilized?  Thank you.
     
    CREATE TABLE [dbo].[Task](
    	[TaskID] [int] IDENTITY(1,1) NOT NULL,
    	[TaskName] [varchar](max) NULL,
     CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
    (
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[Resources](
    	[ResourceID] [int] IDENTITY(1,1) NOT NULL,
    	[FirstName] [nvarchar](50) NULL,
     CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED 
    (
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[TaskResources](
    	[TaskID] [int] NOT NULL,
    	[ResourceID] [int] NOT NULL
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[TaskResources] WITH CHECK ADD CONSTRAINT [fk_TaskResource_pid] FOREIGN KEY([TaskID])
    REFERENCES [dbo].[Task] ([TaskID])
    GO
    ALTER TABLE [dbo].[TaskResources] CHECK CONSTRAINT [fk_TaskResource_pid]
    GO
    
    ALTER TABLE [dbo].[TaskResources] WITH CHECK ADD CONSTRAINT [fk_TaskResource_rid] FOREIGN KEY([ResourceID])
    REFERENCES [dbo].[Resources] ([ResourceID])
    GO
    
    ALTER TABLE [dbo].[TaskResources] CHECK CONSTRAINT [fk_TaskResource_rid]
    GO
    
    Sunday, June 5, 2011 10:32 PM

Answers

  • Try the following:

    declare @TaskID int, @ResourceID int
    insert into Task (TaskName)
    values ('Task1')
    set @TaskID = SCOPE_IDENTITY()
    
    insert into Resources (FirstName) values ('Resource1')
    set @ResourceID = SCOPE_IDENTITY()
    
    insert into TaskResources (TaskID,ResourceID)
    values (@TaskID, @ResourceID)
    
    select T.*, R.* from Task T inner join TaskResources TR on T.TaskID = TR.TaskID
    inner join Resources R on TR.ResourceID = R.ResourceID
    
    You first need to insert tasks and resources and then insert into junction table the created IDs.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by hazz Sunday, June 5, 2011 11:17 PM
    Sunday, June 5, 2011 11:12 PM

All replies

  • Try the following:

    declare @TaskID int, @ResourceID int
    insert into Task (TaskName)
    values ('Task1')
    set @TaskID = SCOPE_IDENTITY()
    
    insert into Resources (FirstName) values ('Resource1')
    set @ResourceID = SCOPE_IDENTITY()
    
    insert into TaskResources (TaskID,ResourceID)
    values (@TaskID, @ResourceID)
    
    select T.*, R.* from Task T inner join TaskResources TR on T.TaskID = TR.TaskID
    inner join Resources R on TR.ResourceID = R.ResourceID
    
    You first need to insert tasks and resources and then insert into junction table the created IDs.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by hazz Sunday, June 5, 2011 11:17 PM
    Sunday, June 5, 2011 11:12 PM
  • thank you so much Naomi.  That makes perfect sense, now that you show me (remind me from several years ago actually) the SCOPE_IDENTITY() feature obtained upon insert and utilized to then place the resultant ID into the linking table.

    With much appreciation!

    Greg

    Sunday, June 5, 2011 11:20 PM