how to insert auto increment value in sql server

Rohan Gupta

11y1.6kLearn iOS Programming

i want to insert auto increment value in sql server..

i have a column order_no nvarchar(200) and want insert a value like this

1001

1002

1003

there is many order number but i update these order number where order number is blank

please help me

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amitesh Verma11y ago
use identity(1000,1) in column
Rajeesh Menoth11y ago
Hi,

Check this : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8f50fbce-38b8-410e-a0e2-6733f77d0b8b/auto-increment-value-in-insert-statement?forum=transactsql
Govinda Rajulu Yemineni11y ago
Hi,

try this

DECLARE @orderno nvarchar(200)
SELECT @orderno=ISNULL(MAX(order_no)+1 ,1001) FROM tablename

you can insert this orderno into your table
Manas Mohapatra11y ago
Hi Rohan,

Yes, you are correct DBCC command work only with identity column with integer datatype.

As per your requirement please follow below sql query:

BusinessEntityID - It is your primary key column
HumanResources].[Employee] - Table name
emp.JobTitle - Column name which you want to update(order_number)

Query:

with tempResult as
(
select BusinessEntityID, CAST((1000 + ROW_NUMBER() OVER(ORDER BY BusinessEntityID DESC)) AS VARCHAR(10)) AS [Row]
from [HumanResources].[Employee]
)

update emp
set emp.JobTitle = temp.[Row]
from [HumanResources].[Employee] emp inner join tempResult temp
on emp.BusinessEntityID = temp.BusinessEntityID
Upendra Pratap Shahi11y ago
set identity in sql server
Rohan Gupta11y ago
suppose i have a column order_numer varchar(200)

order_number
-----------------------
c0124
--------

c01245

---------

now i want update blank field(---) with autoincremented value like this 1001,1002
Rohan Gupta11y ago
This is work with primary key column and my order_no column not primary key not identity column ..

This work with primary key column value with insertation
Manas Mohapatra11y ago
Please execute below command in your database and provide table name also:

DBCC CHECKIDENT (mytable, RESEED, 1000)