CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
bmsqldev
bmsqldev

Reputation: 2735

Auto Increment a non-identity Column in sql-server

We have Non-Identity Column in our Database Which have a specific value . We have a requirement as below,

Whenever a record insert into that column, value should be incremented by one.

how to handle this in sql server ?

Thanks for the help.

Upvotes: 11

Views: 16928

Answers (3)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Well, you can use SEQUENCE statement introduced in SQL Server 2012 brings the method of generating IDs

To use it in insert statement, you need to first create sequence like this - 

CREATE SEQUENCE dbo.Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE

Now use it in your insert statement like this - 

INSERT  INTO dbo.Test1
        ( orderid ,
          custid ,
          empid
        )
        SELECT NEXT VALUE FOR dbo.Id_Sequence,
                @custid ,
                @empid

That's it.

Upvotes: 13

Slasko
Slasko

Reputation: 407

You can load the max value of the table and add +1

SELECT MAX(MyColumn)+1 FROM MyTable

maybe add ISNULL for first run.

ISNULL((SELECT MAX(MyColumn)+1 FROM MyTable),0)

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

Try creating a TRIGGER

CREATE TRIGGER incrementValue
ON Test
FOR Insert
AS 
   Update Test  
   set columnvalue = columnvalue +1 
   where id in (select id from inserted)
GO

Upvotes: 3

Related Questions