-3

Imagine there are two tables with their columns: 1 - T1(Year,ITscore) 2 - T2(Year,EFQMScore)

these two tables are the source tables. There is a destination or target table :

3 - T3(Datekey,ITscore,EFQMScore) [Date key is the primary key]

we want to insert the content of table 1 and table 2 into table 3. what would be the be the best query to do that?

3
  • Can you explain a bit more what the problem is? Perhaps why INSERT... SELECT isn't working for you? – Erik Darling May 6 '17 at 9:11
  • The problem is that i dont know how to insert the content of two tables into one table. – user123800 May 6 '17 at 9:14
  • 2
    Start by writing a SELECT that produces the results you want. Then it's just a matter of adding an INSERT to the mix. – Erik Darling May 6 '17 at 9:41
2

Assuming that t3.Datekey is meant to store a Year and that the relationship between t1 and t2 is a one to one then:

insert into Table3 (DateKey, ITScore, EFQMScore)
select 
    t1.Year,
    t1.ITScore,
    t2.EFQMScore
from
    Table1 t1 inner join Table2 t2 on t1.Year = t2.Year
-1

If (and only if) the INNER JOIN (see Nelson Casanova's answer) does NOT work for you eg if one table contains more year values than the other table, then you may want to use a UNION. Example (I'm using Oracle 12c):

-- Create 2 tables, insert some values
-- We are using odd numbers for years in t_1
-- and even year numbers in t_2 .

create table t_1 (year number, itscore number);
create table t_2 (year number, efqmscore number);

begin
  insert into t_1 values (2011, 3001);
  insert into t_1 values (2013, 3003);
  insert into t_1 values (2015, 3005);
  insert into t_1 values (2017, 3007);

  insert into t_2 values (2010, 6000);
  insert into t_2 values (2012, 6002);
  insert into t_2 values (2014, 6004);
  insert into t_2 values (2016, 6006);
end;

An inner join gives us: "No data found".

    select *
    from t_1 inner join t_2 on (t_1.year = t_2.year) ;
-- -> result: no data found

A "full outer join" returns all years and all scores. However, you want to store years, itscore and efqmscore. Hence, the year columns need to be "merged" (not with a SQL MERGE, though).

select *
from t_1 full outer join t_2 on (t_1.year = t_2.year) ;

-- output
YEAR    ITSCORE YEAR    EFQMSCORE
-       -       2010    6000
-       -       2012    6002
-       -       2014    6004
-       -       2016    6006
2017    3007    -       -
2015    3005    -       -
2013    3003    -       -
2011    3001    -       - 

Thus, I suggest something like this:

select year, itscore, null as efqmstore from t_1 
union
select year, null as itscore, efqmscore from t_2

YEAR    ITSCORE EFQMSTORE
2010    -       6000
2011    3001    -
2012    -       6002
2013    3003    -
2014    -       6004
2015    3005    -
2016    -       6006
2017    3007    - 

If this is roughly the resultset that you want (not sure what the "Date Key" is comprised of, therefore this column is omitted in the following statement), then use the SELECTs in combination with an INSERT:

insert into t_3 (itscore, efqmstore) (
select itscore, null as efqmstore from t_1 
union
select null as itscore, efqmscore from t_2 )

-- if the above statement executes successfully, you can see the inserted rows when you run

select * from t_3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy