Mapping insert table help please...

[peac3]

Hi guys,

I am writing a stored proc and trying to insert table from other table using mapping rules and more complicated thing is the mapping rule is using third table to lookup.

So here's the diagram
table1 (product_code, product_id)
table2 (product_code, product_desc)
table3 (product_id, product_desc)

I would like to insert into table1.product_id from table2.product_desc

but first I need to get the table3.product_id by looking up from table2.product_desc

then insert the table3.product_id into table1.


in sql code I have tried this but not working

insert into table1(product_code,product_id)
select product_code, (select table3.product_id from table2, table3 where table2.product_desc=table3.product_desc)
from table2

Which one is wrong exactly inside my query?

Thank you in advance guys

 

[r937]

INSERT 
  INTO table1
     ( product_code
     , product_id )
SELECT table2.product_code
     , table3.product_id 
  FROM table2
INNER
  JOIN table3 
    ON table3.product_desc = table2.product_desc

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[djj55]

What r937 did not say (but demonstrated nicely) was if you have two tables try and join them.

In my FoxPro days I would have tried to write it like that but my T-SQL skills have improved over the years.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[peac3]

Hi r937,

Thanks,
but I shouldve mentioned earlier, actually in the table2 there are more than one column refer to other tables, I am just giving the example as it contains few hundreds column so it's impossible for me to put all here.

like table2 they have like cust_id, cust_desc which link to other table.

table1 (product_code, product_id, cust,id, supplier_id, ...)
table2 (product_code, product_desc,  cust_desc, supplier_desc,...)
table3 (product_id, product_desc)
[b]table4 (cust_id, cust_desc)[/b]
[b]table5 (supplier_id, supplier_desc)[/b]

so inner_join would narrow the record before it links to other table ie, table3, table4, etc

Do I need a cursor for this?

Thanks guys,

 

[r937]

so inner_join would narrow the record before it links to other table ie, table3, table4, etc

yes

Do I need a cursor for this?

no

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[peac3]

So any idea about solution to insert into table1 without narrow the record and refer to many tables?

 

[r937]

you will have to use many joins

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon