By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,711 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,711 IT Pros & Developers. It's quick & easy.

SQL INSERT INTO a table using information from two other tables

P: n/a
Max
Hi,
I have the following problem. I want to insert records into one table
using the combination of two other tables. Here is a simplified
example: Let's say the first table has a column with RaceCode (which
might have values such as 1,3,5,7, etc), the second table is like a
key for this RaceCode so it has two columns (1st one is 1,3,5,7, etc
and 2nd one with description such as Caucasian, Hispanic, African
American, Native American, etc). Now I want to insert records into the
third table which would look like the first table only instead of
values it will have descriptions but the catch is if the value doesn't
exist in the second table I need to put 'UNKNOWN' How do I accomplish
this? Here is what I have:
Table 1 Table 2 Table 3
-------------------------------------------
CODE CODE DESCRIP DESCRIP
1 1 Cauca Cauca
1 2 Hispa Cauca
2 3 Afri Amer Hispa
7 4 Nati Amer UNKNOWN
2 Hispa
1 Cauca

query = "INSERT INTO table3 (DESCRIP)
SELECT table2.DESCRIP
FROM table1, table2
WHERE table1.CODE = table2.CODE"

The problem is it will not insert the row where the CODE is 7 in
table1 but I want it to be inserted with the value of UNKNOWN. I was
trying to work with IIf function but so far was not successful. Any
help is appreciated!
Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
ma*****@hotmail.com (Max) wrote in message news:<2c**************************@posting.google. com>...
Hi,
I have the following problem. I want to insert records into one table
using the combination of two other tables. Here is a simplified
example: Let's say the first table has a column with RaceCode (which
might have values such as 1,3,5,7, etc), the second table is like a
key for this RaceCode so it has two columns (1st one is 1,3,5,7, etc
and 2nd one with description such as Caucasian, Hispanic, African
American, Native American, etc). Now I want to insert records into the
third table which would look like the first table only instead of
values it will have descriptions but the catch is if the value doesn't
exist in the second table I need to put 'UNKNOWN' How do I accomplish
this? Here is what I have:
Table 1 Table 2 Table 3
-------------------------------------------
CODE CODE DESCRIP DESCRIP
1 1 Cauca Cauca
1 2 Hispa Cauca
2 3 Afri Amer Hispa
7 4 Nati Amer UNKNOWN
2 Hispa
1 Cauca

query = "INSERT INTO table3 (DESCRIP)
SELECT table2.DESCRIP
FROM table1, table2
WHERE table1.CODE = table2.CODE"

The problem is it will not insert the row where the CODE is 7 in
table1 but I want it to be inserted with the value of UNKNOWN. I was
trying to work with IIf function but so far was not successful. Any
help is appreciated!
Thanks.


You were on the right path. Try:

INSERT INTO table3 ( DESCRIP )
SELECT IIf(IsNull(table2.DESCRIP),"UNKNOWN",table2.DESCRI P) AS MyDescrip
FROM table1 LEFT JOIN table2 ON table1.CODE = table2.CODE

Bruce
Nov 12 '05 #2

P: n/a
Thanks a lot it's just what i needed - works great!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.