question

I have 3 Table say TableA(holding data) ,TableB(mapping data) and TableC(data to be inserted).

TableA

Col1                     
----                     
High                     
High                     
Low                      
Right                    
Low                      
Left

TableB

Col1      Col2          
-----------------        
1          High         
2          Low          
3          Left         
4          Right

TableC

Col1                        
----                         
1
1
2
4
2
3

My Dataset is like 3 tables shown above. TableA consists of source data uploaded using bcp.

TableB is a mapping table with static reference values as you can see.

TableC is the table to which now data has to be inserted which is col1 of TableB and equivalent numeric value for Col1 in TableA.

So my doubt is how I write my insert script for TableC and resolve corresponding values for Col1 in TableA. in short I want numeric equivalent of Col1 in TableA to be inserted into TableC by checking on mapping values from TableB.

Thanks in advance