CODEWITHSUNDEEP
sqlsql-server
Sree
Sree

Reputation: 121

How do I add Auto-increment value in SQL Server

I need to generate 7 digit code(Alternate ID) for my existing customers. For example. This my existing Customer Table structure

ID     Name    Address    City   AlternateID
101    Test1    NO 4,..    LA     0000001  
102    Test2    NO 3,..    TA     0000002

We have to run this script on a daily basis. Last number stored in a different table. Next day start from the Last number stored in Table.

(Ex. 1st day added 30 customers means "0000030" 
     2nd day added 10 customers means "0000040")

I want to know the query on how to decrease "0" when digits of the number increased

Like - Single-Digit number "0000001"
     - Double-Digit number "0000010"
     - Triple-Digit number "0000100"

so on.

Upvotes: 0

Views: 200

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use:

update t
    set alternateid = format(row_number() over (order by id) + ot.max_alternateid, '0000000')
    from t cross join
         othertable ot
    where alternateid is null;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do :

select t.*,
       right(concat('0000000', row_number() over (order by id)), 7) as AlternateID
from table t;

Upvotes: 3

Related Questions