Skip to content
Advertisement

Generating sequence numbers

I have table A

ID
---
None
1
3
4
8

I have table B

ID      number
---------------
None    None
 1      300
 3      301
 4      302
 8      303

How do i get values ‘304’, ‘305’….n in B.number column if any new values comes in table A by keeping table B static(i.e keeping the old values in table B unchanged)

Answer

This should work, as you’ve posed the question. But this is a strange thing to need to do, so it might be better to provide less abstract detail on the requirement.

INSERT INTO table_b
    SELECT a.id,
           ( SELECT MAX(number)
               FROM table_b
           ) + ROW_NUMBER() OVER ( PARTITION BY 1 ORDER BY a.id) number
      FROM table_a a
     WHERE NOT EXISTS
             ( SELECT 1
                 FROM table_b b
                WHERE a.id = b.id
             );