issue max value for records inserted all take number 4 i need it 4,5,6,7 ?

Question

I need to insert data to table #t with incremental by 1

so max id for records on #t is 3 so

if i insert multi rows as 4 rows to table #t then it must take 4,5,6,7

but this not happen all records take 4

so how to solve this issue please

i don't need use identity column so how to increment every row by 1 without using identity column
so how to do that please

create table #t  
(  
id int,  
name nvarchar(20)  
)  
insert into #t(id,name)  
values  
(1,'ahmed'),  
(2,'ali'),  
(3,'ala')  
  
create table #t2  
(  
name nvarchar(20)  
)  
insert into #t2(name)  
select 'islam'  
union  
select 'sayed'  
union  
select 'wahdan'  
union  
select 'warshan'  

what i try

insert into #t(id,name)  
select (select isnull(max(id)+1,0) from #t ),name from #t2  

expected result

Answer 1

but this not happen all records take 4

Sure, have a look at the execution plan, the subselect is evaluate only one time.

You can use the ROW_NUMBER function to get a sequence

select (select isnull(max(id), 0)from #t) + ROW_NUMBER() over (order by name) as NewId,
       name 
from #t2

Answer 2

Hi，@ahmed salah
How about using SEQUENCE
Please check this:

IF EXISTS(SELECT * FROM sys.sequences WHERE name = N'TestSeq')  
DROP SEQUENCE TestSeq;  
GO  
CREATE SEQUENCE dbo.TestSeq AS INT  
       START WITH 4  --The first value returned by the sequence object which in this situation is 4  
       INCREMENT BY 1  --The value of the sequence object ascending (or descending if it is a negative number)  
  
INSERT INTO #t(id,name)  
SELECT NEXT VALUE FOR TestSeq OVER(ORDER BY Name ) AS SeqId,name  --If you don’t need to sort by name, just delete the over clause  
FROM #t2  

Note: Use CYCLE to determine whether to cycle.
In order to improve performance, you can also use the CACHE option, so that the serial number can be cached in the memory, reducing IO operations.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.