How to insert a value based on lookup from another table [SQL]?

Question

I need to find a way to do an INSERT INTO table A but one of the values is something that comes from a lookup on table B, allow me to illustrate.

I have the 2 following tables:

Table A:

A1: String
A2: Integer value coming from table B
A3: More Data

Table B:

B1: String
B2: Integer Value

Example row of A: {"Value", 101, MoreData} Example row of B: {"English", 101}

Now, I know I need to INSERT the following into A {"Value2", "English", MoreData} but obviously that won't work because it is expecting an Integer in the second column not the word "English", so I need to do a lookup in Table B first.

Something like this:

INSERT INTO tableA (A1, A2, A3) 
VALUES ("Value2", SELECT B2 FROM tableB where B1="English", MoreData);

Obviously this doesn't work as-is ...

Any suggestions?

Answer 1

how about:

Insert into tableA ( a1,a2,a3) 
(select "value2", b2, moreData from TableB where B1 = "English")