Key column auto increment

Question

I have a table with a column set_id has sequence number in it, I am planning to add a couple more records to the table. Now how do I do the auto increment of the sequence of the number?

Example: I have the max as 37 now, so when I add a new record I want it to start from 38 and so on, how do I write a SQL query for it?

insert into abc.data (set_id, set_cd, set_desc, type_cd, mgt_cd, mgt_desc, itm_cd, upd_dt, upd_by)
values (38, 111, other, S, total, totalwww, 0000434, 27-feb-17, kiran)

and so on ..

How can I increment to 39... max?

I removed the incompatible database tags. Please tag with the database you are really using. — Gordon Linoff, Feb 27 '17 at 20:40
It's not clear if you are looking for the syntax of an auto increment column (SQL Server: `identity(1,1)` MySQL `AUTO_INCREMENT`) or for the `identity insert` — Raul, Feb 27 '17 at 20:47
Don't ever think about "sequence numbers" being consecutive integers with no gaps. For one thing, there is no benefit from that. For another, how do you propose to maintain that when you need to delete a row from the table - are you going to update every remaining row (or almost)? How is that going to work when you have multiple users inserting rows, deleting or updating them in the table, at the same time? — , Feb 27 '17 at 21:46

SqlZim · Answer 1 · 2017-02-27T20:45:27.233

(you have also tagged mysql)

For sql-server: you can use set identity_insert ...

set identity_insert abc.data on;

 insert into abc.data
  (set_id,set_cd,set_desc,type_cd,mgt_cd,mgt_desc,itm_cd,upd_dt,upd_by)
 values (38,111,other,S,total,totalwww,0000434,27-feb-17,kiran)

set identity_insert abc.data off;

Or just not insert into that column and let identity handle it:

insert into abc.data
  (set_cd,set_desc,type_cd,mgt_cd,mgt_desc,itm_cd,upd_dt,upd_by)
values (111,other,S,total,totalwww,0000434,27-feb-17,kiran)

If you are using a sequence then you can use next value for dbo.sequencename:

 insert into abc.data
  (set_id,set_cd,set_desc,type_cd,mgt_cd,mgt_desc,itm_cd,upd_dt,upd_by)
 values (next value for dbo.mysequence,111,other,S,total,totalwww,0000434,27-feb-17,kiran)

Key column auto increment

1 Answers1