Generating primary key based on maximum value

Question

I have database tables TableA and TableA_Stage with same columns and I want to copy the records in TableA_Stage to TableA generating primary key based on maximum value in TableA.

I tried this sql and it didn't work because same primary key value gets inserted :

insert into tableA(col_pk, col1, col2)
  select (
          select max(col_pk)+1 
            from tableA
          ),
         col1, col2
   from tableA_Stage

I am looking for sql statement to copy records

Maybe you should just set autoincrement on the table and use `intert into tableA (col1, col2) select col1, col2 from tableA_Stage`? — Tomasz Kapłoński, Oct 10 '13 at 17:56
Maybe duplicate of http://stackoverflow.com/questions/798766/how-to-create-an-oracle-sequence-starting-with-max-value-from-a-table — schlenk, Oct 10 '13 at 19:46
@schlenk, the link you provided creates a sequence object. I want to copy without creating a new sequence object. — Jay, Oct 10 '13 at 19:58
Are you aware that this won't work in a multi-user environment? And it's definitely not scalable because it will get slower the bigger TableA gets. — , Oct 10 '13 at 21:03
Have you tried `select (select max(a.id) from a) + rownum, ... from table2`? — Amir Pashazadeh, Oct 10 '13 at 22:01
@a_horse_with_no_name The performance of this will be fine if there's an index on the col_pk of table_a and it's constrained to not null. It just needs a coalesce or nvl to ensure that an empty table_a does not cause a nul col_pk to be generated. — David Aldridge, Oct 11 '13 at 15:27
@DavidAldridge: that doesn't make it correct in a multi-user environment though — , Oct 11 '13 at 16:08
@a_horse_with_no_name Agreed -- but if no multiuser environment, this works fine for tasks like loading reporting tables and whatnot. I used it for a data warehouse very successfully -- very particular circumstances of course. — David Aldridge, Oct 11 '13 at 18:06

score 0 · Answer 1 · answered Oct 10 '13 at 17:56

0

This is that what you want to achieve. But it is not good idea to do so.

insert into tableA
(col_pk, col1, col2)
select (select max(col_pk) from tableA) + col_pk,
       col1, col2
from tableA_Stage

answered Oct 10 '13 at 17:56

Hamlet Hakobyan

32,965
6
52
68

score 0 · Answer 2 · answered Oct 10 '13 at 18:08

0

Maybe you should generate your keys as:

select
  Coalesce((select max(col_pk) from tablea),0) + rownum
  col2,
  ...

answered Oct 10 '13 at 18:08

David Aldridge

51,479
8
68
96

The generated Ids should follow/continue the primary key values in main table. – Jay Oct 10 '13 at 18:49
Which is the main table? – David Aldridge Oct 11 '13 at 08:32
tableA is the main table. – Jay Oct 11 '13 at 15:22
The code selects the maximum current value from table, using 0 if the table is empty, and adds 1 onto it for every row inserted. This technique is fine as long as you only have one process ever inserting into that table, by the way. – David Aldridge Oct 11 '13 at 15:25

score 0 · Answer 3 · answered Oct 10 '13 at 21:02

0

You need to find the difference between the maximum PK value in tableA and the minimum PK value in tableA_Stage and add that difference plus 1 to the PK value from tableA_Stage. You're simply adding 1 to the max PK value from tableA, which gives the same value for every single row. You probably want something like this:

insert into tableA(col_pk, col1, col2)
  select (
          (select max(col_pk) from tableA) - (select min(col_pk) from tableA_Stage) + 1 + col_pk
          ),
         col1, col2
   from tableA_Stage

answered Oct 10 '13 at 21:02

GriffeyDog

8,186
3
22
34

@Griffey.thx for the reply. The generate PK should continue/follow the sequence in tableA for the new records inserted from tableA_stage. IF the tableA has PK sequence as 1, 2, 3...20501, the new PK value should be 20502 and so on. – Jay Oct 11 '13 at 14:39
@Jay, that's exactly what the statement I wrote should do. – GriffeyDog Oct 14 '13 at 13:48

Generating primary key based on maximum value

3 Answers3