issue max value for records inserted all take number 4 i need it 4,5,6,7 ?
-
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 #t2expected result
- This topic was modified 2 years, 9 months ago by ahmed_elbarbary.2010. Reason: sql
-
The following code will achieve your requirement.
However, you may want to add code to prevent duplicates
INSERT INTO #t( id, name )
SELECT ISNULL((SELECT MAX(id) FROM #t), 0) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, name
FROM #t2; -
I like @desnorton's answer.
However, what about just using the SEQUENCE object? It'll do the increments for you. It doesn't have to be specifically an IDENTITY column that way.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Building your own sequential numbers is a sure fire way to generate duplicates by accident. Even Des Norton notes that for his code.
Please explain why you're trying to avoid an IDENTITY column because that, or as Grant mentions, a SEQUENCE are the 2 best ways there are. What is it about IDENTITY that you're trying to avoid?
We need to know such things because if, for example, you're trying to avoid gaps in the sequence, neither IDENTITY nor SEQUENCE will guarantee that. There IS a way to avoid such things as GAPs but I don't want to waste my time if that's not the reason why you're trying to avoid IDENTITY.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply