1

i have missing records in a sequence and my current output looks like this

|       1882 |   25548860 |         4 | 30                  | null      |       null |
|       1882 |   25548861 |         4 | 30                  | null      |       null |
|       1882 |   25548882 |         4 | 30                  | null      |       null |
|       1882 |   25548883 |         4 | 30                  | null      |       null |
|       1882 |   25548884 |         4 | 30                  | null      |       null |
|       1882 |   25548885 |         4 | 30                  | null      |       null |
                         missing records in between until 2122
|       2122 |   25548860 |         4 | 30                  | null      |       null |
|       2122 |   25548861 |         4 | 30                  | null      |       null |
|       2122 |   25548882 |         4 | 30                  | null      |       null |
|       2122 |   25548883 |         4 | 30                  | null      |       null |
|       2122 |   25548884 |         4 | 30                  | null      |       null |
|       2122 |   25548885 |         4 | 30                  | null      |       null |

I want my output to be in below format. Suggest me a sql query that will update the records in monetdb between 1883 to 2121.

|       1882 |   25548860 |         4 | 30                  | null      |       null |
|       1882 |   25548861 |         4 | 30                  | null      |       null |
|       1882 |   25548882 |         4 | 30                  | null      |       null |
|       1882 |   25548883 |         4 | 30                  | null      |       null |
|       1882 |   25548884 |         4 | 30                  | null      |       null |
|       1882 |   25548885 |         4 | 30                  | null      |       null |

|       1883 |   25548860 |         4 | 30                  | null      |       null |
|       1883 |   25548861 |         4 | 30                  | null      |       null |
|       1883 |   25548882 |         4 | 30                  | null      |       null |
|       1883 |   25548883 |         4 | 30                  | null      |       null |
|       1883 |   25548884 |         4 | 30                  | null      |       null |
|       1883 |   25548885 |         4 | 30                  | null      |       null |
     ........   ..........
     ........   ..........
|       2122 |   25548860 |         4 | 30                  | null      |       null |
|       2122 |   25548861 |         4 | 30                  | null      |       null |
|       2122 |   25548882 |         4 | 30                  | null      |       null |
|       2122 |   25548883 |         4 | 30                  | null      |       null |
|       2122 |   25548884 |         4 | 30                  | null      |       null |
|       2122 |   25548885 |         4 | 30                  | null      |       null |
GMB
  • 216,147
  • 25
  • 84
  • 135
kilmonger
  • 41
  • 7
  • Do you want to actually insert the extra rows in the table, or just to display them as a result of the query? – The Impaler Oct 10 '20 at 11:20
  • Can you please show the query you are using? – BoilermakerRV Oct 10 '20 at 11:21
  • @TheImpaler i want to insert the records – kilmonger Oct 10 '20 at 11:23
  • @BoilermakerRV i'm just using a basic select statement to query my table where i have missing records from 1883 to 2121. The table follows a trend and gets iterative after 6th row. So, I want to insert records in the desired format as mentioned above. – kilmonger Oct 10 '20 at 11:27

2 Answers2

0

If you know in advance the range of missing ids, you can use generate_series(). Assuming that your table is called mytable and has columns (id, col1, col2, col3, col4, col5), you can duplicate the records that have id 1882 to fill the gap with the following query:

insert into mytable (id, co11, col2, col3, col4, col5)
select value, col1, col2, col3, col4, col5
from sys.generate_series(1883, 2121, 1)
cross join mytable t
where t.id = 1882
GMB
  • 216,147
  • 25
  • 84
  • 135
0

Assume the schema of your table is something like:

create table mytable(id int, col1 int, col2 int, col3 int, col4 int, col5 int);

You can fill in the "missing records" with:

insert into mytable
select *, 4, 30, null, null 
from sys.generate_series(1884, 2121, 1),
    (select distinct col1 from mytable where id = 1883) as tmp;

However, the new records will be appended to the existing records, so if you want to have them returned in the order you showed above, you need an additional order by:

select * from mytable order by id, col1;
Jennie
  • 345
  • 1
  • 8