how to insert auto increment value in sql server

Question

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

Manas Mohapatra · Answer

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

Manas Mohapatra · Answer

Please execute below command in your database and provide table name also: DBCC CHECKIDENT ( mytable , RESEED, 1000)

Amitesh Verma · Answer

use identity(1000,1) in column

Rajeesh Menoth · Answer

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 Yemineni · Answer

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

Upendra Pratap Shahi · Answer

set identity in sql server

Rohan Gupta · Answer

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 Gupta · Answer

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

how to insert auto increment value in sql server

Answers (8)