Sql query to add missing rows from another table

Question

I have a table A (columns: id, year,month,amount) and table B( columns: id, year,month, amount=0).

Table A is the master table which have a same set of ids for each month for every year from 2011 to 2016.

In that some records are not present (like their is no record for 123456 id for 03 month and 2016 year and 468976 amountk), for that I want to add new record having id 123456, 03 in month and 2016 in year in table A and as it is a missing record amount will be 0.

The missing records are taken from table B which is having same set of ids from table A and for each id it is having every month for every year from 2011 to 2016, for each row amounts as 0.

Note: 1. In table A record s are not sorted and in table records are grouped by id, year and month. 2. If possible please make it normal sql queries instead of pl/sql. If not possible as I wanted please suggest your answer.

Thanks in advance........I hope you understsnd the problem statement.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jan 12 '17 at 19:55
http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 shows how to write a query that finds all the rows in table A that aren't table B. You can then use that in an `INSERT INTO tableB SELECT` query to insert all the missing rows. — Barmar, Jan 12 '17 at 20:14

score 0 · Answer 1 · answered Jan 12 '17 at 20:14

You can generate the records using a cross join and some more logic:

select i.id, ym.year, ym.month
from (select distinct year, month from a) ym cross join
     (select distinct id from b) i left join
     a
     on a.year = ym.year and a.month = ym.month and b.id = i.id
where a.id is null;

(I think I have the as and bs right. I'm not sure why two separate tables are mentioned in the question. It would seem that a is missing the records, so it is the only table needed. My best guess is that b is a subset of ids in a.)

Here is a version that does the insert:

insert into a(id, year, month)
    select i.id, ym.year, ym.month
    from (select distinct year, month from b) ym cross join
         (select distinct id from b) i left join
         b
         on b.year = ym.year and b.month = ym.month and b.id = i.id
    where b.id is null;

For my ease I took that approach of using table B. Now my problem is : How to add missing rows in a table A which has same structure as above. I want to check for a id (in table A no id is present with amount, month and year having null values, so while inserting id also should be taken care) which month and year is missing and insert that with amount as 0. — Saran, Jan 12 '17 at 20:47

Sql query to add missing rows from another table

1 Answers1