CODEWITHSUNDEEP
sqlsql-server
Vic Street
Vic Street

Reputation: 168

How to create a gradually-incrementing column in SQL Server?

How can I create a column of data that increments gradually? EG. 1 1 1 2 2 2 3 3 3 etc

CREATE TABLE #tmp (FirstName VARCHAR(50), LastName VARCHAR(50), Increment INT)

INSERT INTO #tmp (FirstName, LastName, Increment) SELECT 'Alice', 'Bell', NULL
INSERT INTO #tmp (FirstName, LastName, Increment) SELECT 'Charlie', 'Dickens', NULL
INSERT INTO #tmp (FirstName, LastName, Increment) SELECT 'Ernest', 'Fabio', NULL
INSERT INTO #tmp (FirstName, LastName, Increment) SELECT 'Graham', 'Holst', NULL
INSERT INTO #tmp (FirstName, LastName, Increment) SELECT 'India', 'Joplin', NULL

SELECT * FROM #tmp

Upvotes: 0

Views: 205

Answers (3)

Vadim
Vadim

Reputation: 414

You can use identity column + computed column. Sure it's two columns instead of one, but it's good to have identity column for a primary key anyway.

create table #t(data varchar(50), id int identity(0,1), increment as convert(int, id / 3))

insert into #t (data) select 'test1'
insert into #t (data) select 'test2'
insert into #t (data) select 'test3'
insert into #t (data) select 'test4'
insert into #t (data) select 'test5'
insert into #t (data) select 'test6'
insert into #t (data) select 'test7'
insert into #t (data) select 'test8'
insert into #t (data) select 'test9'

select * from #t

Upvotes: 2

Daniel Gimenez
Daniel Gimenez

Reputation: 20494

You can create a table with a default constraint associated to a sequence. If you want every third value to increment then just divide by three in the constraint:

CREATE SEQUENCE MySeq  
    START WITH 3  
    INCREMENT BY 1 ;  
GO 

CREATE TABLE MyTable (
    FirstName VARCHAR(50)
    , LastName VARCHAR(50)
    , Increment INT CONSTRAINT IncrementDefault DEFAULT (NEXT VALUE FOR [MySeq]/(3))
)
GO

INSERT INTO MyTable (FirstName, LastName) SELECT 'Alice','Bell'
INSERT INTO MyTable (FirstName, LastName) SELECT 'Charlie','Dickens'
INSERT INTO MyTable (FirstName, LastName) SELECT 'Ernest','Fabio'
INSERT INTO MyTable (FirstName, LastName) SELECT 'Graham','Holst'
INSERT INTO MyTable (FirstName, LastName) SELECT 'India','Joplin'

The results will be

FirstName  | LastName  | Increment
Alice      | Bell      | 1
Charlie    | Dickens   | 1
Ernest     | Fabio     | 1
Graham     | Holst     | 2
India      | Joplin    | 2

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

The only way to do such Increment 1, 1, 1, 2, 2, 2, 3, 3, 3 is to create a SEQUENCE :

CREATE SEQUENCE MySeq AS INT  
    START WITH 1  
    INCREMENT BY 1 
    MAXVALUE 3 
    MINVALUE 1
    CYCLE;  
GO 

CREATE TABLE MyTable (
    FirstName VARCHAR(50)
    , LastName VARCHAR(50)
    , Increment INT --CONSTRAINT IncrementDefault DEFAULT (NEXT VALUE FOR [MySeq]/3)
)
GO

INSERT INTO MyTable (FirstName, LastName, Increment) SELECT 'Alice','Bell', ( NEXT VALUE FOR [MySeq])
INSERT INTO MyTable (FirstName, LastName, Increment) SELECT 'Charlie','Dickens', ( NEXT VALUE FOR [MySeq])
INSERT INTO MyTable (FirstName, LastName, Increment) SELECT 'Ernest','Fabio', ( NEXT VALUE FOR [MySeq])
INSERT INTO MyTable (FirstName, LastName, Increment) SELECT 'Graham','Holst', ( NEXT VALUE FOR [MySeq])
INSERT INTO MyTable (FirstName, LastName, Increment) SELECT 'India','Joplin', ( NEXT VALUE FOR [MySeq])

SELECT * FROM MyTable ORDER BY Increment ASC;

Results:

+-----------+----------+-----------+
| FirstName | LastName | Increment |
+-----------+----------+-----------+
| Alice     | Bell     |         1 |
| Graham    | Holst    |         1 |
| India     | Joplin   |         2 |
| Charlie   | Dickens  |         2 |
| Ernest    | Fabio    |         3 |
+-----------+----------+-----------+

Upvotes: 1

Related Questions