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

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?

score 9 · Answer 1 · answered Apr 21 '10 at 16:22

9

how about:

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

answered Apr 21 '10 at 16:22

Avitus

15,640
6
43
53

Doesn't seem to work ... complains about syntax near SELECT. If this is the standard approach then I must have a typo somewhere (looking into it now). But, a friend of mine just told me the following "In an VALUES clause, you can only specify variables, or constants". I assume he is wrong (there has to be a way) – Shaitan00 Apr 21 '10 at 17:51
Maybe it's the parentheses around the `select...` part? Particularly in SQL Server they are not needed there and possibly illegal too. Don't know about other server products, though. – Andriy M Jan 30 '11 at 11:28

score 2 · Answer 2 · answered Jul 24 '12 at 08:21

2

It seems to be more cross-DBMS without parentheses, as @Andriy M commented:

insert into tableA (a1, a2, a3) 
select 'value2', b2, moreData from tableB where B1='English'

answered Jul 24 '12 at 08:21

bluish

26,356
27
122
180

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

2 Answers2