Sql Query for inserting data from 2 tables into one based on name

Hello I currently 2 tables of data fro开发者_StackOverflow中文版m different sources, I need to combine all of them into one main table.

Database layout:

Table A

-Name

-Ranking

-Score

Table B

-Name

-Ranking

-Score

Table New

-Name

-Ranking A

-Score A

-Ranking B

-Score B

I want to take the data from table A and B and insert it into table New based on the name. Not sure how to do this in sql, any help appreciated

Assuming every record in TableA has a corresponding record in TableB:

insert into TableNew
    (Name, RankingA, ScoreA, RankingB, ScoreB)
    select a.Name, a.Ranking, a.Score, b.Ranking, b.Score
        from TableA a
            inner join TableB b
                on a.Name = b.Name

If that assumption is invalid, then:

insert into TableNew
    (Name, RankingA, ScoreA, RankingB, ScoreB)
    select a.Name, a.Ranking, a.Score, b.Ranking, b.Score
        from TableA a
            left join TableB b
                on a.Name = b.Name
    union all
    select b.Name, a.Ranking, a.Score, b.Ranking, b.Score
        from TableB b
            left join TableA a
                on b.Name = a.Name
        where a.Name is null

Try this(should work in Oracle, SQL Server, MySQL): To Create the third table(if it doesn't exist):

CREATE TABLE [TableNew] AS
SELECT  a.Name
                ,a.Ranking RankingA
                ,a.Score ScoreA
                ,b.Ranking RankingB
                ,b.Score ScoreB
  FROM  TableA a, TableB b
 WHERE  a.Name = b.Name

To Insert into third table(if it exists):

INSERT INTO [TableNew]
SELECT  a.Name
                ,a.Ranking RankingA
                ,a.Score ScoreA
                ,b.Ranking RankingB
                ,b.Score ScoreB
  FROM  TableA a, TableB b
 WHERE  a.Name = b.Name