How to increment the value during insert programmatically

Question

Declare @Order INT=0

INSERT INTO MyTable(col1,col2,ordercol) values('abc','xyz',++@Order) 

this way order value is not getting incremented, but i found this way value gets incremented during update
declare @i int = SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices

update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

code taken from https://stackoverflow.com/questions/13629382/update-int-column-in-table-with-unique-incrementing-values

if in case of update a variable value getting incremented but the same approach fail during insert...Why?

i saw people advise go for this approach to increment value during insert. here is code example

insert into Table1 (column1,column2)
select 
    isnull(T1.m, 0) + row_number() over (order by T2.anotherColumn),
    T2.anotherColumn
from Table2 as T2
    outer apply (select max(column) as m from Table1) as T1

OR

Insert into table1 (col1, col2, col3, col4)
select col1, 
    isnull(( select max(isnull(col2,0))  from table1 ),0) + ( ROW_NUMBER() over (order by col2) ),
    col3, col4 
from table2

please tell me few approach by which i can increment value and insert into column. thanks

Answer 1

if in case of update a variable value getting incremented but the same approach fail during insert...Why?

Because T-SQL is not C++.

If you think this would be a useful feature to have in T-SQL, you can submit your suggestions here: https://feedback.azure.com/forums/908035-sql-server

Answer 2

Hi @Sudip Bhatt ,

At the end of the question, you provide two ways to solve the problem of increasing the value during insert.
Did you try to apply them？Both methods seem to be feasible.

Do you understand the auto-increment column in sql?When inserting new data into the data table, the auto-increment column is skipped, and the column will grow according to the set auto-increment rule without inserting.
example:

IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL    
   DROP TABLE new_employees;    
GO    
CREATE TABLE new_employees    
(    
 id_num int IDENTITY(1,1),    
 fname varchar (20),    
 minit char(1),    
 lname varchar(30)    
);    
    
INSERT new_employees    
   (fname, minit, lname)    
VALUES    
   ('Karin', 'F', 'Josephs');    
    
INSERT new_employees    
   (fname, minit, lname)    
VALUES    
   ('Pirkko', 'O', 'Koskitalo');  
  
SELECT * FROM new_employees     

Output:

id_num fname minit lname  
1	Karin	F	Josephs  
2	Pirkko	O	Koskitalo  

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table