I have three tables in my database.

Table one effectively contains two fields:

|Datetime| |Set|

Table two is a lookup table which matches a number of parts of a set to a set number:

|Set| |Part1| |Part2| |Part3| |Part4|

I want table 3 to have a record for each part in a set for a particular datetime:

|Datetime| |Part|

where the populated table would look something like:

|12:00:00| |Set1_Part1|
|12:00:00| |Set1_Part2|
|12:00:00| |Set1_Part3|
|12:00:00| |Set1_Part4|
|12:02:30| |Set2_Part1|
|12:02:30| |Set2_Part2|
|12:02:30| |Set2_Part3|
|12:02:30| |Set2_Part4|

So I get some information in table 1 about a set and a datetime, then table 3 needs to effectively extrapolate that out into a datetime/part pair for each part in the set.

Any ideas? (This is for SQL Server)

Perhaps something like...

Insert Into table3 (
Select datetime, set + '_' + Part1
FROM table1
INNER JOIN table2
  on T1.Set=T2.Set
  and Part1 is not null
UNION
Select datetime, set + '_' + Part2
FROM table1
INNER JOIN table2
  on T1.Set=T2.Set
  and Part2 is not null
UNION
Select datetime, set + '_' + Part3
FROM table1
INNER JOIN table2
  on T1.Set=T2.Set
 and Part3 is not null
UNION
Select datetime, set + '_' + Part4
FROM table1
INNER JOIN table2
  on T1.Set=T2.Set
 and Part4 is not null
UNION
Select datetime, set + '_' + Part5
FROM table1
INNER JOIN table2
  on T1.Set=T2.Set
 and Part5 is not null

)

more