Steve, I think this is what you want:
INSERT INTO Tb2 + (U_FirstName, U_LastName, U_Address1, U_City U_StateID, U_Zip) + SELECT + First_Name, Last_Name, Address, City, st1.State_ID, tb1.Postal_Code + FROM tb1, st1 + WHERE tb1.state = st1.state_abbrev Bill On Wed, 3 Sep 2003 12:53:34 -0500, Fogelson, Steve wrote: >I have 3 tables. One is a table (TB1) of names and addresses with the State >in abbreviated format (IE: MN). One is a table (TB2) of name and addresses >with the State as an ID that relates to the third table (ST1) of states with >an ID and abbreviation. >I want to insert rows from TB1 into TB2 and translate the abbreviation into >an ID based on ST1. The following doesn't work as it seems to use the 1st >SELECT. Any ideas? >INSERT INTO TB2 U_FirstName, U_LastName, U_Address1, U_City, (SELECT >ST1.State_ID FROM ST1 WHERE ST1.State_Abbrev=TB1.State), U_Zip >SELECT First_Name, Last_Name, Address, City, State, Postal_code FROM TB1
