Add column with increment default value by 1

Transact-SQL
1

Hi All.
I would like to add column with default value auto increment by 1 to existing table. If is it possible how to do it?

Thanks.

2

You might want to look into sequence

3

Hi bitsmed. Thanks for replay.
I would like that value in this column will increase by 1 when new record will inserted to the table.

Thanks.

4

As I wrote, look into sequence https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017

5

Would IDENTITY not be OK?

drop table if exists #temp

create table #temp (
	wibble varchar(10)
)
insert into #temp
select * from (values ('wobble'),('bobble'),('whatever')) as dummy(d)

select * from #temp

alter table #temp
add incrementer integer not null identity(1,1)

select * from #temp

insert into #temp
select * from (values ('some'),('more'),('data')) as dummy(d)

select * from #temp
1 Like
6

Hi uberbloke. Thanks for replay.

I have table with primary key which is identity and auto increment 1 by 1. And would like to add to that table new field that value will increment 1 by 1. If is it possible how to do that?

Thanks.

8

OK, so then you will need to look at sequence like bitsmed said

Example updated with a bit of sequencing

use tempdb;

drop table if exists #temp
go

create table #temp (
	wibble varchar(10)
)
insert into #temp
select * from (values ('wobble'),('bobble'),('whatever')) as dummy(d)

select * from #temp

alter table #temp
add incrementer integer not null identity(1,1)

select * from #temp

insert into #temp
select * from (values ('some'),('more'),('data')) as dummy(d)

select * from #temp

drop sequence if exists mySequence

create sequence mySequence
start with 1
increment by 1
no cache
;

alter table #temp
add myIncrementer INTEGER constraint abc default (next value for mySequence) 

select * from #temp

-- use the sequence
update #temp 
set myIncrementer = (next value for mySequence)

select * from #temp

-- and again, just for fun

update #temp 
set myIncrementer = (next value for mySequence)

select * from #temp

-- exercise the default
insert into #temp (wibble)
select * from (values ('need'),('more'),('rows')) as dummy(d)

select * from #temp