CODEWITHSUNDEEP
sqlpostgresql
MJ95
MJ95

Reputation: 479

Increment column IDs by 1 in insert into statement

I have a table I want to insert into based on two other tables.

In the table I'm inserting into, I need to find the Max value and then do +1 every time to basically create new IDs for each of the 2000 values I'm inserting.

I tried something like 

MAX(column_name) + 1

But it didn't work. I CANNOT make the column an IDENTITY and ideally the increment by one should happen in the INSERT INTO ... SELECT ... statement.

Many Thanks!

Upvotes: 1

Views: 2638

Answers (3)

user330315
user330315

Reputation:

Use a sequence, that's what they are for. 

create sequence table_id_sequence;

Then adjust the sequence to the current max value:

select setval('table_id_sequence', (select max(id_column) from the_table));

The above only needs to be done once.

After the sequence is set up, always use that for any subsequent inserts:

insert into (id_column, column_2, column_3)
select nextval('table_id_sequence'), column_2, column_3
from some_other_table;

If you will never have any any concurrent inserts into that table (but only then) you can get away with using max() + 1

insert into (id_column, column_2, column_3)
select row_number() over () + mx.id, column_2, column_3
from some_other_table
   cross join (
      select max(id_column) from the_table
   ) as mx(id);

But again: the above is NOT safe for concurrent inserts.

The sequence solution is also going to perform better (especially if the target table grows in size)

Upvotes: 1

cableload
cableload

Reputation: 4375

If its mysql, you could do something like this..

insert into yourtable 
select 
@rownum:=@rownum+1 'colname', t.* from yourtable t, (SELECT @rownum:=2000) r

The example to generate rownumber taken from here

If its postgresql, you could use 

insert into yourtable 
    select t.*,((row_number() over () ) + 2000) from yourtable t

Please note the order for the select is different on both the queries, you may need to adjust your insert statement accordingly.

Upvotes: 1

IsraelSistemas
IsraelSistemas

Reputation: 21

You can declare a variable with the last value from the table and put it on the insert statement, like this:

DECLARE @Id INT SET @Id = (SELECT TOP 1 Id FROM YoutTable ORDER BY DESC)

INSERT INTO YourTable VALUES (@Id, Value, Value)

Upvotes: 1

Related Questions