Tuesday, September 30, 2014

Challenge Scenario: Inserting data into a third Table

Sometimes you may find problem like below:

You have table called “Country_Code” that holds country code and name like below:
CTY_CODE
Country_name
001
USA
86
China
46
Sweden
45
Denmark

Now you have same group_code and communication rating scale value for all above country.
e.g.
group_code=09 and rating_scale_value=7

Your task is to insert data to the second table which called “Code_Rating_value”

Data should look like below:
CTY_CODE
group_code
Rating_scale_value
001
09
7
86
09
7
46
09
7
45
09
7

If it’s small amount of data then it’s easy to insert by following:
Insert into Code_Rating_value
Values(001,09,7)

Execute the above statement by changing the value four times and you are done!!
But if it’s a big table with thousands of records you will not going to do this thousands of times, will you??

You will find the below code to insert the data at once.

insert into Code_Rating_value
select  CTY. CTY_Code, Rating_value .group_code, Rating_value. Rating_scale_value
from
(select '09' as group_code, '07’ as Rating_scale_value , '1' as key) as Rating_value
left join (select distinct CTY_Code, '1' as key
from Country_Code) as CTY

 on CTY. key = Rating_value. Key

1 comment:

Anonymous said...

This is one more complicated way you have arrived at the solution:
simply write:

insert into Code_Rating_value
select distinct CTY_Code, '09' , '7'
from Country_Code;