Inserting Rows

I have an item table (#Codes) which contains itemIDs, some of which are always used in a subsequent data table (#Data) to hold information about a case+scearnio. These item rows have a standarditem value = 1. Rows in the item table which may be included in the subsequent data table (#Data), but not for every single case+scearnio, are considered nonstandard rows and therefore have a standarditem value = 0. I have been trying to write some code that automatically inserts rows from #Codes which have standarditem = 1 into #Data for each of the caseID+scenarioIDs in #Cases but have not figured it out yet. See "desired result" below to see exactly the output I'm trying to achieve.

Also, is there a way to insert the standarditem = 1 rows from #Codes into #Data when new caseID+scenarioID rows are added to #Cases? Ultimately, I'd like to either create a button for the user to click in the Access interface that inserts these rows when clicked OR when the user enters a new caseID+scenarioID in #Cases the rows just automatically appear for the new case row. This way, the rows will already have the caseID, scenarioID & itemID fields already populated and all the user will have to do is enter the item value and be able to manually add standarditem = 0 rows and their values if needed.





Code Block

create table #Codes (itemID nvarchar(2), standarditem int)
insert into #Codes values (1,1)
insert into #Codes values (2,0)
insert into #Codes values (3,1)
insert into #Codes values (4,1)
insert into #Codes values (5,0)

create table #Cases (caseID nvarchar(5), scenarioID nvarchar(15), createdate datetime)
insert into #Cases values (823, 1, '20071210')
insert into #Cases values (823, 2, '20071211')
insert into #Cases values (824, 1, '20071213')

create table #Data (caseID nvarchar(5), scenarioID nvarchar(15), itemID nvarchar(2), itemvalue decimal (18,0))


data in #Codes:
itemID standarditem
1 1
2 0
3 1
4 1
5 0

data in #Cases:
caseID scenarioID createdate
823 1 2007-12-10 00:00:00.000
823 2 2007-12-11 00:00:00.000
824 1 2007-12-13 00:00:00.000


desired result in #Data:
caseID scenarioID itemID itemvalue
823 1 1 <null>
823 1 3 <null>
823 1 4 <null>
823 2 1 <null>
823 2 3 <null>
823 2 4 <null>
824 1 1 <null>
824 1 3 <null>
824 1 4 <null>


Thanks in advance for your help.