Processing a resultset to look up foriegn keys (and poulate a new table!)
- by Gilly
Hi, I've been handed a dataset that has some fairly basic table structures with no keys at all. eg
{myRubishTable} - Area(varchar),AuthorityName(varchar),StartYear(varchar),StartMonth(varcha),EndYear(varchar),EndMonth(varchar),Amount(Money)
there are other tables that use the Area and AuthorityName columns as well as a general use of Month and Years so I I figured a good first step was to pull Area and Authority into their own tables.
I now want to process the data in the original table and lookup the key value to put into my new table with foreign keys which looks like this.
(lookup Tables)
{Area} - id (int, PK), name (varchar(50))
{AuthorityName} - id(int, PK), name(varchar(50)
(TargetTable)
{myBetterTable} - id (int,PK), area_id(int FK-Area),authority_name_id(int FK-AuthorityName),StartYear (varchar),StartMonth(varchar),EndYear(varchar),EndMonth(varchar),Amount(money)
so row one in the old table read
MYAREA, MYAUTHORITY,2009,Jan,2010,Feb,10000
and I want to populate the new table with
1,1,1,2009,Jan,2010,Feb,10000
where the first '1' is the primary key and the second two '1's are the ids in the lookup tables.
Can anyone point me to the most efficient way of achieving this using just SQL?
Thanks in advance
Footnote:- I've achieved what I needed with some pretty simple WHERE clauses (I had left a rogue tablename in the FROM which was throwing me :o( ) but would be interested to know if this is the most efficient.
ie
SELECT [area].[area_id],
[authority].[authority_name_id],
[myRubishTable].[StartYear],
[myRubishTable].[StartMonth],
[myRubishTable].[EndYear],
[myRubishTable].[EndMonth],
[myRubishTable].[Amount]
FROM [myRubishTable],[Area],[AuthorityName]
WHERE [myRubishTable].[Area]=[Area].[name]
AND [myRubishTable].[Authority Name]=[dim_AuthorityName].[name]
TIA
