Processing a resultset to look up foriegn keys (and poulate a new table!)

Question

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

Answer 1

Following your footnote, other than replacing your syntax of

FROM X, Y, Z
WHERE X.a = Y.a AND X.b = Z.b

with the equivalent but more idiomatic SQL of

FROM
   X
   INNER JOIN Y ON X.a = Y.a
   INNER JOIN Z ON Z.b = Z.b

I don't think there's much to add. For something that will only be run once (right?), efficiency shouldn't really be your prime concern - correctness should be. Unless of course you have so much data or such an inefficient method that it does actually take too long, of course...